Power Query added column - Value.Divide(Value.Add( )) returns all null values

[astrbac]

Hi all,


When I create a calculated field in Microsoft Power Query feature ("Get and Transform" since Excel 2016) I use a combination two simple formulae: Value.Divide() and Value.Add(). For some reason, this does not work and all I get is null values.

Here is the series of steps that I usually do:

1. Get&Transform data from .csv;
2. Arrange fields (columns) by dragging around, rename;
3. Add some custom fields;
4. Change field types as appropriate (if necessary);

The one giving me problems is as follows:

= Table.AddColumn(#"Add Cost per result", "ROAS", each Value.Divide(Value.Add([Website conversion value], [#"Mobile app purchases conversion value (corr.)"]), [#"Amount spent (GBP)"]))

Or, as copied from a "graphical interface":

= Value.Divide(Value.Add([Website conversion value], [#"Mobile app purchases conversion value (corr.)"]), [#"Amount spent (GBP)"])

How fields relevant for this formula appear in the Get&Transform window:

• [Website conversion value] is imported from the original .csv;
• [#"Mobile app purchases conversion value (corr.)"] is calculated field (via "Add column...");
• [#"Amount spent (GBP)"] is imported from the original .csv (I suspect that this might be the problematic one - why does it have a # pre-pended when it was in the .csv originally, during the import? Isn't this a symbol # for a "table from previous step"?;

Many thanks! Alex

 

[astrbac]

SOLVED!

It seems that the Power Query Value. formulae cannot work with null values. I replaced null values with a 0 (which is not the most correct thing to do but what can I do) and it worked!