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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Mate, this is a demonstration of how it should work in M. You take this, inspect this, understand it and then apply to your model.

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

        Anonymous 

        thanks i guess i get into it. 2 questions left.

        to avoid that all other columns are removed, must i add them to {"KundeID"}?

         

         

            #"Group" 				= Table.Group(#"Add Column 2", {"KundeID"}, {{"GroupYear", each _, type table [KundeID=text, Jahr=text]}}),

         

         

        And so far so good. Is there a way to reduced the result per row to a distinct value?

        Thanks in beforehand.

        Greetings Sam