Add Custom Column

This M code performs calculations on a dynamic or changing column names.

Average

The first number the column number from the left to use, for this example it is the second column. The second number is the columns from the right hand side to use, in this case it is none, as our pivot goes right to the end.

= List.Average(List.RemoveLastN(List.RemoveFirstN(Record.FieldValues(_),1),0))

Sum

The first number the column number from the left to use, for this example it is the second column. The second number is the columns from the right hand side to use, in this case it is none, as our pivot goes right to the end.

= List.Sum(List.RemoveLastN(List.RemoveFirstN(Record.FieldValues(_),1),0))

Convert Nulls to 0s

If you have null values in your dynamic column list that you wish to convert to 0, you can use the below code in your Power Query - Advanced Editor

#"allColumnNames" = Table.ColumnNames(#"Pivoted Column"),
#"allTranformations" = List.Transform(#"allColumnNames", each {_, each if _ = null or _ = "" then 0 else _}),
#"tranformColumns" = Table.TransformColumns(#"Pivoted Column",#"allTranformations"),

Source

https://stackoverflow.com/questions/69496685/which-column-has-a-max-in-power-query-table

  • No labels