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 IDProgramsPrograms-all
ABCTelemarketingTelemarketing; Online Ad
ABCOnline AdTelemarketing; Online Ad
BBDWeb Web; Conference
BBDConferenceWeb; Conference
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"

7 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

    If not, please provide your expected output.

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

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        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"
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Perhaps:

     

     

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

     

    • 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