Forum Discussion

KristofferAJ's avatar
KristofferAJ
Helper III
4 years ago
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 ro...
Need Help
Show and Tell
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"