Transform multiple statusChanges to a single entry (a list separated by delimiters, ordered by date)

[Anonymous]

Hi there!

I would like to transform table A to table B - DAX or Power Query, no specific preferences!

Table A
ChangedDate	ID	NewStatus
1-Jan-20	1	Trial
3-Apr-20	1	Subscribed
2-Jul-20	1	Suspended
4-Feb-20	2	Free
7-May-20	3	Trial

to

Table B
ID	Journey
1	Trial, Subscribed, Suspended
2	Free, Trial

Thank you in advance 🙂

[amitchandak · Accepted solution]

@Anonymous , try concatenatex

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

new measure

Journey = concatenatex(Table,Table[NewStatus])

[Anonymous]

@amitchandak  This worked perfectly as a New Column, thank you!

[Anonymous]

You can use powerquery to achieve this:

Group by ID

Select "all rows"

Add a column:

Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped], "NewStatus"))

Click expand on the new column, and select extract values

select comma

That's it, let me know if you need more help!

[Anonymous]

@Anonymous For some reason I end up with the correct answer, BUT all for the same ID... I think I'll go with concatenateX this time. Thank you!