Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
ID | GroupCode |
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 |
10 | STFP |
11 | @STFP |
12 | @PAYP |
12 | @STFP |
13 | @PF |
13 | @STFP |
13 | STFP |
14 | @STFP |
15 | @STFP |
16 | @STFP |
17 | @STFP |
18 | @STFP |
19 | @STFP |
20 | @STFP |
21 | @CRD |
21 | @PAYP |
21 | @STFP |
21 | STFP |
22 | @STFP |
23 | @STFP |
24 | @STFP |
25 | @STFP |
25 | STFP |
I want the data to look like this:
ID | GroupCode |
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 |
25 | STFP |
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.
Solved! Go to Solution.
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.
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.
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.
try the below code in measure,
Concatenatex =
CALCULATE(
CONCATENATEX(Code,Code[GroupCode],","),
ALLEXCEPT(Code,Code[ID]))
Thanks,
Arul
You can use Power Query in Excel or Power BI to achieve this. Here are the steps:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
64 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |