Forum Discussion

IgorGershenson's avatar
IgorGershenson
Icon for Helper II rankHelper II
6 years ago
Solved

custom formula in Query mode that lists all distinct values for a certain variable (see example)

  My query creates the two columns on the left, but how do I add the third column that lists all distinct program values for each account?  Please help. Thanks   Account ID Programs Programs-...
Need Help
Show and Tell
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    Take a look at this M code, since you said "Query"

     

    It generates this table

     

    I did it in multiple steps so you could see how each worked.

     

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCknNSc1NLMpOLcnMS1eK1YGJ++flZOalKjimgMWcnFyAYuGpSQpIXOf8vLTUotS85FSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, Programs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID", type text}, {"Programs", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account ID"}, {{"All Rows", each _, type table [Account ID=text, Programs=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Programs Column", each Table.SelectColumns([All Rows], "Programs")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.ToList([Programs Column])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Extracted Values", "All Rows", {"Programs"}, {"Programs"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"Account ID", "Programs", "Custom"})
in
    #"Removed Other Columns"
IgorGershenson's avatar
IgorGershenson
Icon for Helper II rankHelper II
6 years ago

IS there any way to create the same kind of logic using the M code in a query? 

camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
6 years ago

Hi IgorGershenson ,

 

Try this m-code:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1HwdFHSUQooyk8vSswtRmLqJubkKMXqABU6OQOFQ1JzUnMTi7JTSzLz0tH51gr+eTmZeakKjilIWhBiBJQ7OYHcEJ6apAChrBWc8/PSUotS85JTkRQgCWJRFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(blank).2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account ID", type text}, {"Programs", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Account ID", Text.Trim, type text}, {"Programs", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"Account ID"}, {{"Rows", each _, type table [Account ID=text, Programs=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine(Table.ToList(Table.Distinct(Table.SelectColumns([Rows], "Programs"))), ",")),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns1", "Custom", "Custom - Copy"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Custom - Copy", "Programs-all"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Programs-all", type text}})
in
#"Changed Type3"

 

Ricardo