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
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous,

        I tried to get into this

         

         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]),

         

        but do i need this in whole?

         

        Where does that "AllCustYears" come from?

         

        #"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"})