Search code examples
powerbidaxpowerquerym

Power Query custom column with M calculating % with null values

In power query, how might I use M to create a custom column "% column" where there are sometimes null values in my reference columns A, B and C.

Without null values, it's straightforward. I could write =([A]+[B]+[C])/3.

However, what can I do to discount the null values? Row 2 would need to be [A]+[B]/2 And Row 3 would be [B]+[C]/2.

enter image description here

Many thanks

Solution

  • duplicate your query. right click ID column ... unpivot other columns Unpivoting removes null items automatically

    right click ID, group by, operation average on value column. File .. close and load ... connection only ... to save that query

    Go to your original query. Home .. merge queries ... choose the current query for the top, the other query for the bottom and click on ID column in both of them, use a left outer join. Use arrow atop the new column to expand the average column

    You can do it in a single query with some manual code editing in home ...advanced .. as in below example

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),

// create average
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"ID"}, {{"Average", each List.Average([Value]), type number}}),

// merge back to original data
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ID"},#"Grouped Rows",{"ID"},"GR",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "GR", {"Average"}, {"Average"})
in #"Expanded Table2"