Power Query custom column with M calculating % with null values

Question

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.

Many thanks

horseyride horseyride · Accepted Answer · 2021-04-19T15:14:45

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"

Power Query custom column with M calculating % with null values

1 Answers