Results 1 to 3 of 3

Thread: Power Query add two columns with a null value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2006
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query add two columns with a null value

    I have a data set and I am trying to simply add to columns together, both columns are decimal type. Some of the values are null in one column or the other and no matter what I have tried the sum always returns a null value in the calculated column for those rows with null values in one column.

    I have tried replacing all null with 0 and I have tried writing a formula so it only includes the non null value in the formula.

    Any ideas to solve this would be much appreciated.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Power Query add two columns with a null value

    Try this formula replacing Col1 and Col2 with your column/field names.

    (if [Col1]=null then 0 else [Col1])+(if [Col2]=null then 0 else [Col2])
    If posting code please use code tags.

  3. #3
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query add two columns with a null value

    Just use List.Sum:

    Code:
    = Table.AddColumn(Source, "Addition", each List.Sum({[Number1],[Number2]}), type number)
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •