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

astrbac

Board Regular
Joined
Jan 22, 2015
Messages
55
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:

Code:
= 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":

Code:
= 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
 
Last edited:

astrbac

Board Regular
Joined
Jan 22, 2015
Messages
55
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! :)
 

Forum statistics

Threads
1,085,586
Messages
5,384,592
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top