how to create a multiple value column from rows for grouping benefit

[Tony_Kuiper]

Hi there... I have a table that I would like to change the layout on as far as summarising a column.

client_id, milestone_name, date

abc, first milestone, 01/01/2022

abc, second milestone, 01/01/2022

abc, third milestone, 02/01/2022

abc, fourth milestone, 03/01/2022

abc, fifth milestone, 03/01/2022

I can work out the elapsed time before each milestone change, but would like to view the data as

client_id, date, milestones

abc, 01/01/2022, {first milestone, second milestone}

abc, 02/01/2022, {third milestone}

abc, 03/01/2022, {fourth milestone, fifth milestone}

I cannot remember how to do this. I've been trawling through functions, but I'm not connecting the dots.

Tony

[v-yueyunzh-msft · Accepted solution]

Hi, @Tony_Kuiper 

Here are the steps you can refer to :

(1) This is my test data which is the same as yours:

(2)If you want to create a table , you can click "New Table" and enter :

Table = SUMMARIZE('Sheet1','Sheet1'[client_id],'Sheet1'[date] , "name" , CONCATENATEX( DISTINCT( VALUES('Sheet1'[milestone_name])) , [milestone_name] , ","))

Then we can meet your need , the result is as follows：

(3)If you want to show in the visual ,you can create a measure :

Name = CONCATENATEX( DISTINCT( VALUES('Sheet1'[milestone_name])),'Sheet1'[milestone_name] ,  ",")

Then we put the measure and the field we need in the visual and we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ppm1]

If you just need to view it like that in a table/matrix visual, you can use an expression like this

List of Milestones = CONCATENATEX(VALUES(Table[Milestone_Name]), Table[Milestone_Name], ", ")

Pat