Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Hi there!

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

 

Table A
ChangedDateIDNewStatus
1-Jan-201Trial
3-Apr-201Subscribed
2-Jul-201Suspended
4-Feb-202Free
7-May-203Trial

 

to

 

Table B
IDJourney
1Trial, Subscribed, Suspended
2Free, Trial

 

Thank you in advance 🙂

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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