Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.