Create Array (List) with DAX

[Anonymous]

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]

Is this not an easy job for Power Query?

Best
D

[Anonymous]

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 · Accepted solution]

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]

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]

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]

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

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]

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

[Mariusz]

Hi @Anonymous 

You can use CONCATINATEX()

CONCATENATEX( <table>, <expression>, [delimiter] )  

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

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]

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