Search code examples
mergenullpowerbim

Merging Columns with null values PowerQuery

I have: 

Name  Value
A     null
B     null
null  5
null  10

and I need:

Name  Value
A     5
B     10

Thanks a lot for providing a solution.

Solution

  • I had asked a similar question, to which @MarcelBeug provided a very helpful response; which, in turn, I'm using as the basis for my answer to you for your specific table.

    This requires you to use Power Query (Power BI's query editor).

    For your situation, I...

    1. added a column named "Group", with the word "Group" in every one of its rows
    2. then I used "Group By" on that new Group column, using sum aggregation for both the Name and Value columns

    enter image description here enter image description here

    1. then I edited the code that was generated in step 2...changing the occurences of List.Sum to List.RemoveNulls
    2. then I added a column with an embedded table from the two lists that resulted from step 3

    enter image description here

    1. then I deleted all columns other than the Tabled column
    2. then I expanded the Tabled column, which gave me this:

    enter image description here

    Here's the M code:

    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each "Group"),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"NameList", each List.RemoveNulls([Name]), type text}, {"ValueList", each List.RemoveNulls([Value]), type number}}),
#"Added Custom.1" = Table.AddColumn(#"Grouped Rows", "Tabled", each Table.FromColumns({[NameList],[ValueList]},{"Name","Value"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom.1",{"Tabled"}),
#"Expanded Tabled" = Table.ExpandTableColumn(#"Removed Other Columns", "Tabled", {"Name", "Value"}, {"Name", "Value"})
in
#"Expanded Tabled"