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, 2019" or "2017, 2019". Just if the customer was active in a year, i want to get the years in one row.

 

Is something like that possible?

 

Thanks a kind regards.

Sam

  • 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

11 Replies

    • 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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Is this not an easy job for Power Query?

    Best
    D
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi darlove,

      guess it's easy with M but not for an mere user like me :). Which formula shall I use to perform the output in power query?

      • Anonymous's avatar
        Anonymous
        Not applicable
        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