Forum Discussion
How can I convert/pivot repeating rows in the same column into individual columns for a single line?
I need assistance converting the following table:
| Name | Directory Contact | Directory Contact Title | Directory Contact Email | Directory Contact Phone |
| Acme | Elmer Fudd | Lead Hunter | [email protected] | 555-555-5555 |
| Acme | Bugs Bunny | Director of Carrot Intake | [email protected] | 444-444-4444 |
| Acme | Wile E. Coyote | Director of Traps | [email protected] | 333-333-3333 |
| Disney | Mickey Mouse | Actor | [email protected] | 111-111-1111 |
| Disney | Goofy Dog | Clown | [email protected] | 222-222-2222 |
I want the final result to look like this:
| Company Name | Directory Contact 1 | Directory Contact Title 1 | Directory Contact Email Address 1 | Directory Contact Phone 1 | Directory Contact 2 | Directory Contact Title 2 | Directory Contact Email Address 2 | Directory Contact Phone 2 | Directory Contact 3 | Directory Contact Title 3 | Directory Contact Email Address 3 | Directory Contact Phone 3 |
| Acme | Elmer Fudd | Lead Hunter | [email protected] | 555-555-5555 | Bugs Bunny | Director of Carrot Intake | [email protected] | 444-444-4444 | Wile E. Coyote | Director of Traps | [email protected] | 333-333-3333 |
| Disney | Mickey Mouse | Actor | [email protected] | 111-111-1111 | Goofy Dog | Clown | [email protected] | 222-222-2222 |
Anonymous
Refer to this post which does exactly what you need.
https://excelgorilla.com/power-bi/power-query/aggregate-text-values-using-group-by/
After you modify the group by M query, the table will look like below and use Split column with delimiter option to split each values into individual columns.
Here is the M query for group by ..
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Directory Contact", each Text.Combine([Directory Contact],","), type text}, {"Directory Contact Title", each Text.Combine([Directory Contact Title],","), type text}, {"Directory Contact Email", each Text.Combine([Directory Contact Email],","), type text}, {"Directory Contact Phone", each Text.Combine([Directory Contact Phone],","), type text}})If it helps, mark it as a solution
Kudos are nice too
3 Replies
Anonymous
Refer to this post which does exactly what you need.
https://excelgorilla.com/power-bi/power-query/aggregate-text-values-using-group-by/
After you modify the group by M query, the table will look like below and use Split column with delimiter option to split each values into individual columns.
Here is the M query for group by ..
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Directory Contact", each Text.Combine([Directory Contact],","), type text}, {"Directory Contact Title", each Text.Combine([Directory Contact Title],","), type text}, {"Directory Contact Email", each Text.Combine([Directory Contact Email],","), type text}, {"Directory Contact Phone", each Text.Combine([Directory Contact Phone],","), type text}})If it helps, mark it as a solution
Kudos are nice too