Merge Rows into one row parsing two columns

[StefanoP]

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

[ronrsnfld]

 - 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"

[StefanoP]

i don't get how to apply it sorry

[ronrsnfld]

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.

[StefanoP]

I'm sorry I don't get it well.

[ronrsnfld]

What did you try?

What happened when you ran the code?

What went wrong?

[BA_Pete]

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

[StefanoP]

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...