none
Add row even where null value appearing

    Question

  • Hi,

    In the Power Query window, I have 5 columns titled as Custom10.column1, Custom10.column2, Custom10.column3, Custom10.column4 and Custom10.column5.  Some cells show null whereas others show numbers.

    I created the following custom column formula to obtain a row wise sum of the five columns

    =InsertedCustom2 = Table.AddColumn(RenamedColumns, "Custom", each [Custom10.Column1]+[Custom10.Column2]+[Custom10.Column3]+[Custom10.Column4]+[Custom10.Column5])

    The result appears correct is all cells have numbers.  For rows where all cells are not numbers i.e. some are null, the result is blank.

    How can I get the sum for even those rows where there is a null appearing?

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, October 21, 2013 3:03 PM

Answers

  • Hi Ashish,

    You will have to replace null with 0 for the aggregate to work. You can do this by right-clicking on the column(s) containing null values and selecting "Replace Values...".

    Thanks,
    M.

    Tuesday, October 22, 2013 1:07 AM

All replies