Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create Array (List) with DAX

Hi There,   I have Different Customers [Column] in Different Years [Column]. Now I want to create a New Table I guess, with the Customer and a Array which includes the Years like this "2017, 2018, ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Put this M code into Advanced Editor in PQ and execute. You'll see how it's done.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLjFU0lEyMjAwUIrVgQgYoQtAVBiiq0ASMEbXYoxdhRG6ocYIAROIGUboAsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Year = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"AllCustYears", each _, type table [Customer=text, Year=text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "OnlyYears", each [AllCustYears][Year]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"AllCustYears"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Years", each Text.Combine([OnlyYears], ",")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"OnlyYears"})
in
    #"Removed Columns1"

     

    Best

    D

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Mariusz,

     

    i tried this Formula but with 4 Million Rows, it works really really slow (didn't finish at all after 30 Min). What is happening in this formula? And is it more capable than the M Formula when it's all about performance?

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      Hi Anonymous 

       

      I can imagine it will be, Anonymous  power query solution probably will be a better option

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      LinkedIn