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

  • 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 :).