Forum Discussion

dphillips's avatar
dphillips
Icon for Helper IV rankHelper IV
3 years ago
Solved

Change multiple rows to one column with delimiter

I am trying to do the opposite of split column. I have 2 columns - 1 with an ID and the other with a group code. At the moment the column looks like below. An ID may have multiple rows. I want to have only one row for each ID and put each of the different codes in the second column with a delimiter of some sort eg a comma. See second table. 

 

IDGroupCode
1@STFP
2@STFP
3@STFP
4@STFP
5@CRD
5@STFP
6@STFP
7@CRD
7@PAY
7@STFP
8@STFP
9@STFP
10@STFP
10STFP
11@STFP
12@PAYP
12@STFP
13@PF
13@STFP
13STFP
14@STFP
15@STFP
16@STFP
17@STFP
18@STFP
19@STFP
20@STFP
21@CRD
21@PAYP
21@STFP
21STFP
22@STFP
23@STFP
24@STFP
25@STFP
25STFP

 

I want the data to look like this:

IDGroupCode
1@STFP
2@STFP
3@STFP
4@STFP
5@CRD, @STFP
6@STFP
7@CRD, @PAY, @STFP
8@STFP
9@STFP
10@STFP, STFP
11@STFP
12@PAYP, @STFP
13@PF, @STFP, STFP
14@STFP
15@STFP
16@STFP
17@STFP
18@STFP
19@STFP
20@STFP
21@CRD, @PAYP, @STFP, STFP
22@STFP
23@STFP
24@STFP
25@STFP
25STFP
26@PAYP

 

What is the simplest/best way of doing this? How can I transform my data?

Finally, I would love to have a slicer which has all the unique group codes, and, when filtering, will return all the rows which have that code in, even if it is just one of the groups codes out of 2 or 3 or 4? eg If I filter on @CRD  it will return rows 5, 7 and 21. Any help would be much appreciated.

Need Help
  • dphillips's avatar
    dphillips
    3 years ago

    Thanks MAwwad for the info and have managed to work out exactly what to do myself. As suggested, I grouped the data by ID and used the "All Rows" aggregation. Then I removed the first column.

    = Table.AddColumn(#"Grouped Rows", "Custom", each Table.RemoveColumns([Codes],"ID"))

    Then I transposed that single column:

    = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom]))

    I then expanded this table using all fields - this gave me 6 columns as the person with the most Groupcodes had 6 of them.

    Then I simply merged those 6 columns using Text.Combine:

    = Table.AddColumn(#"Expanded Custom.1", "AllCodes", each Text.Combine({[Custom.1.Column1], [Custom.1.Column2], [Custom.1.Column3], [Custom.1.Column4], [Custom.1.Column5], [Custom.1.Column6]}, ","), type text)

    Finally - I removed all the other columns and this left me with the result I needed:

    = Table.RemoveColumns(#"Inserted Merged Column",{"Codes", "Custom", "Custom.1.Column1", "Custom.1.Column2", "Custom.1.Column3", "Custom.1.Column4", "Custom.1.Column5", "Custom.1.Column6"})

     

    My problem, now, is how to filter the column which has merged values, using just one of those values.

4 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    dphillips ,

    try the below code in measure,

    Concatenatex = 
CALCULATE(
    CONCATENATEX(Code,Code[GroupCode],","),
    ALLEXCEPT(Code,Code[ID]))

    Thanks,

    Arul

     

  • MAwwad's avatar
    MAwwad
    Icon for Solution Sage rankSolution Sage
    3 years ago

    You can use Power Query in Excel or Power BI to achieve this. Here are the steps:

    1. Select the table and load it into Power Query.
    2. Group the table by the ID column, and then use the "All Rows" aggregation option on the GroupCode column.
    3. Use the "Join Values" option to concatenate the values in the GroupCode column with a delimiter of your choice.
    4. Load the transformed table back into Excel or Power BI.

    For the slicer, you can create a new slicer based on the GroupCode column and set it to "Single Select" mode. When you select a value in the slicer, it will filter the table to show only the rows that contain that value in the GroupCode column.

  • dphillips's avatar
    dphillips
    Icon for Helper IV rankHelper IV
    3 years ago

    Thanks MAwwad. No problems grouping by the ID column but not sure about the "Join Values" option. Where / how do I do that? I can't see that option anywhere. See below where I am at the moment. I have grouped by ID and have used the All Rows aggregation. I can't see the join values option to concatenate values. The final "Column" in the second screenshot is always greyed out. 

     

     

     

     

     

     

     

    • dphillips's avatar
      dphillips
      Icon for Helper IV rankHelper IV
      3 years ago

      Thanks MAwwad for the info and have managed to work out exactly what to do myself. As suggested, I grouped the data by ID and used the "All Rows" aggregation. Then I removed the first column.

      = Table.AddColumn(#"Grouped Rows", "Custom", each Table.RemoveColumns([Codes],"ID"))

      Then I transposed that single column:

      = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom]))

      I then expanded this table using all fields - this gave me 6 columns as the person with the most Groupcodes had 6 of them.

      Then I simply merged those 6 columns using Text.Combine:

      = Table.AddColumn(#"Expanded Custom.1", "AllCodes", each Text.Combine({[Custom.1.Column1], [Custom.1.Column2], [Custom.1.Column3], [Custom.1.Column4], [Custom.1.Column5], [Custom.1.Column6]}, ","), type text)

      Finally - I removed all the other columns and this left me with the result I needed:

      = Table.RemoveColumns(#"Inserted Merged Column",{"Codes", "Custom", "Custom.1.Column1", "Custom.1.Column2", "Custom.1.Column3", "Custom.1.Column4", "Custom.1.Column5", "Custom.1.Column6"})

       

      My problem, now, is how to filter the column which has merged values, using just one of those values.