- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Merge Rows into one row parsing two columns
Hello there, hope this is not a redundant thread.
As in picture, I got a table where are stored users for certain applications and their roles.
It could be reaaally nice to have one row for every user that use that system and all the roles granted...on one row obviously.
I tried to make transpose transformations and pivoting columns found on some guides on the web, but it's related to one column.
Here I have two major columns to deal with ( System and Users ) and fill unkwon role ( because a user can have only one kind of grant for that system ) with null or NA ( whatever ).
So, this is an example of what I have
SYSTEM | USER | ROLE |
sys1 | usr1 | role 1 |
sys1 | usr1 | role 2 |
sys1 | usr1 | role 3 |
sys1 | usr1 | role 4 |
sys1 | usr1 | role 5 |
sys1 | usr1 | role 6 |
sys1 | usr2 | role 1 |
sys2 | usr1 | role1 |
This is what I crave for
SYSTEM | USER | ROLE 1 | ROLE 2 | ROLE 3 | ROLE 4 | ROLE 5 | ROLE 6 |
sys1 | usr1 | role1 | role2 | role3 | role4 | role5 | role6 |
sys1 | usr2 | role 1 | null | null | null | null | null |
sys2 | usr1 | role1 | null | null | null | null | null |
I'm a bit of stuck...can someone give me help?
Bests
Stefano
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Group by SYSTEM and USER
- Aggregate into a List of Roles
- Expand the list into new columns
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKq4sNlTSUSotLgJRRfk5qQqGSrE6WCWMcEkY45IwwSVhikvCDE3CCMNVRqg6gOKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SYSTEM = _t, USER = _t, ROLE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SYSTEM", type text}, {"USER", type text}, {"ROLE", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SYSTEM", "USER"}, {
{"Role Count", each Table.RowCount(_)},
{"ROLE", each [ROLE]}}),
#"Number of Roles" = List.Max(#"Grouped Rows"[Role Count]),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Role Count"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns",
{"ROLE", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "ROLE",
Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), #"Number of Roles")
in
#"Split Column by Delimiter"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

i don't get how to apply it sorry
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Paste the code I provided into the Advanced Editor of Power Query (Transform). If you don't know how to do that, go through some tutorials in Power BI and Power Query to find out.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I'm sorry I don't get it well.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

What did you try?
What happened when you ran the code?
What went wrong?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Stefano,
This looks like an XY Problem waiting to happen.
What are actually trying to achieve overall, as you're moving the data away from the most efficient structure here.
Power Query isn't really designed to format your data how you want it to look, but rather to transform/aggregate it into an efficient dataset for formatting later in Excel or Power BI.
Pete
Now accepting Kudos! If my post helped you, why not give it a thumbs-up?
Proud to be a Datanaut!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Well, I'm sorry for that.
Still, I don't know how to do it neither in powerbi, I'm trying to figure out how...

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |