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

No. You have to create a reference to the original table, remove duplicates according to your rules, then perform the aggregation as above and then join back to the original table selecting the columns you want.

It's best to learn something about Power Query.

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

Anonymous 

Jeah I made it, created a second table (in excel i needed to make another query) delete all columns except Customer and Year, make distincts with both columns. Than use your formula and it worked perfect (don't forget to set a relation).

 

 

    #"Andere entfernte Spalten" = Table.SelectColumns(#"Add Column 2",{"KundeID", "Jahr"}),
    #"Entfernte Duplikate"      = Table.Distinct(#"Andere entfernte Spalten"),
    #"Group" 			= Table.Group(#"Entfernte Duplikate", {"KundeID"}, {{"GroupYear", each _, type table [KundeID=text, Jahr=text]}}),
    #"Add Custom 1" 		= Table.AddColumn(#"Group", "OnlyYears", each [GroupYear][Jahr]),
    #"Removed Column 1" 	= Table.RemoveColumns(#"Add Custom 1",{"GroupYear"}),
    #"Add Custom 2" 		= Table.AddColumn(#"Removed Column 1", "Jahre", each Text.Combine([OnlyYears], ",")),
    #"Removed Column 2" 	= Table.RemoveColumns(#"Add Custom 2",{"OnlyYears"})

 

 Thanks a lot for those hints :).