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

[IgorGershenson]

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-all
ABC	Telemarketing	Telemarketing; Online Ad
ABC	Online Ad	Telemarketing; Online Ad
BBD	Web	Web; Conference
BBD	Conference	Web; Conference

[Greg_Deckler]

Perhaps:

Programs-all DAX Column =
  CONCATENATEX(
    DISTINCT(
      SELECTCOLUMNS(
        FILTER('Table','Table'[Account ID] = EARLIER('Table'[Account ID])),
        "__Programs",'Table'[Programs]
      )
    ),
    [__Programs],
    ";"
  )

[IgorGershenson]

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

[camargos88]

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

[IgorGershenson]

I will check this out. thanks

[edhans]

isn't that what the Programs column is, a list of the distinct values?

If not, please provide your expected output.

[IgorGershenson]

Sorry. I was unclear. The column called Programs all (on the right) is my expected output. That is the one I want to create but not sure how or what formula will work here. 

[edhans · Accepted solution]

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"