Forum Discussion

Tony_Kuiper's avatar
Tony_Kuiper
Helper I
3 years ago
Solved

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

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

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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

     

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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