Forum Discussion
Solved
Add column in powerQuery which counts values from other columns in my model
Hi,
I have 5 coumns in my model which I want to use to have a 'count' column enabling me to see how many of these columns are populated by row..
IN excel I would do below, getting a number by row indicating how many of the targeting columns contains a value/are not blank..
My question: How can I have this directly as a column in powerquery -> add column I persume?
Use below in a custom column
= List.NonNullCount(Record.ToList(_))See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMYFasTrWQE4UMpsBBCFUzEBMGFipgCGWlAnAHEFkBsBtOJqswcwjaGYqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t, #"Column E" = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column A", "Column B", "Column C", "Column D", "Column E"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.NonNullCount(Record.ToList(_))) in #"Added Custom"
1 Reply
Use below in a custom column
= List.NonNullCount(Record.ToList(_))See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMYFasTrWQE4UMpsBBCFUzEBMGFipgCGWlAnAHEFkBsBtOJqswcwjaGYqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t, #"Column E" = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column A", "Column B", "Column C", "Column D", "Column E"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.NonNullCount(Record.ToList(_))) in #"Added Custom"