De-duplicate Id column by splitting out columns in power query

[Anonymous]

Hello.

My current table in Power Query looks like this:  (As you can see there are multiple rows per combination of ID and Risk Owner.

ID	Role Name
24656	Risk Owner	John Smith
24656	Risk Owner	Amy Brown
24657	Risk Owner	Dave Bloggs
24658	Risk Owner	Richard Lee
24659	Risk Owner	Katy Baker

My desired output is like this where each ID has its own unique row but additional columns where there are additional Risk Owners.  Each ID has a maximum of 3 so the table won't be too wide.  Any help would be much appreciated.  Thanks. 

ID	Role Name	Risk Owner 1	Risk Owner 2
24656	Risk Owner	John Smith	Amy Brown
24657	Risk Owner	Dave Bloggs
24658	Risk Owner	Richard Lee
24659	Risk Owner	Katy Baker

[Peter_Beck · Accepted solution]

Hi -

Here is one possible solution.

Essentially you create an index for each distinct group, and then create new columns based on the word "owner" and that index number.

(Creating the index by each group is covered here: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query)

Then you pivot based on this, and do a "fill up" and "fill down" to create duplicate rows where there is more than one name who is an owner.

Finally, you remove the duplicate rows.

Paste this into the Advanced Editor and you will see what I mean.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrKLM4GUv7lealFCl75GXkKwbmZJRlKsTpY5B1zKxWcivLL88DSRujSLollmSkKTvnp6cVgBcboCoIykzMSi1IUfFJTwQpM0BV4J5YAbUjMTi1Sio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Role = _t, Owner = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Role", type text}, {"Owner", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Owner", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Owner.1", "Owner.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Owner.1", type text}, {"Owner.2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"Count", each _, type table [ID=nullable number, Role=nullable text, Owner.1=nullable text, Owner.2=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID", "Role", "Owner.1", "Owner.2", "Index"}, {"Custom.ID", "Custom.Role", "Custom.Owner.1", "Custom.Owner.2", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ID", "Count"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.ID", "Custom.Role", "Custom.Owner.1", "Custom.Index", "Custom.Owner.2"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Owner Plus Index", each [Custom.Owner.1] & Number.ToText([Custom.Index])),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Custom.ID", "Custom.Role", "Custom.Owner.1", "Custom.Index", "Owner Plus Index", "Custom.Owner.2"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"[#"Owner Plus Index"]), "Owner Plus Index", "Custom.Owner.2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"}),
#"Filled Up" = Table.FillUp(#"Removed Columns1",{"Owner2"}),
#"Filled Down" = Table.FillDown(#"Filled Up",{"Owner1"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down", {"Owner1"})
in
#"Removed Duplicates"

Hope this helps!

Peter

[Jakinta · Accepted solution]

Here is another way. Just use steps below after your Source step.

Group = Table.Group(Source, {"ID", "Role Name"}, {{"A", each Text.Combine ( List.Transform( Table.ToRows (_), List.Last), "/") }}),
    ColNames = List.Transform ({1..List.Max(Table.AddColumn( Group, "Count", each  Text.Length (Text.Select ([A], {"/"} ) ) +1 )[Count])}, each "Risk Owner " & Text.From(_)), // To get dynamic Column Names for Column Splitting in the next step
    #"Split Column by Delimiter" = Table.SplitColumn(Group, "A", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), ColNames)
in
    #"Split Column by Delimiter"

[Peter_Beck · Accepted solution]

Hi -

Here is one possible solution.

Essentially you create an index for each distinct group, and then create new columns based on the word "owner" and that index number.

(Creating the index by each group is covered here: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query)

Then you pivot based on this, and do a "fill up" and "fill down" to create duplicate rows where there is more than one name who is an owner.

Finally, you remove the duplicate rows.

Paste this into the Advanced Editor and you will see what I mean.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrKLM4GUv7lealFCl75GXkKwbmZJRlKsTpY5B1zKxWcivLL88DSRujSLollmSkKTvnp6cVgBcboCoIykzMSi1IUfFJTwQpM0BV4J5YAbUjMTi1Sio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Role = _t, Owner = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Role", type text}, {"Owner", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Owner", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Owner.1", "Owner.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Owner.1", type text}, {"Owner.2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"Count", each _, type table [ID=nullable number, Role=nullable text, Owner.1=nullable text, Owner.2=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID", "Role", "Owner.1", "Owner.2", "Index"}, {"Custom.ID", "Custom.Role", "Custom.Owner.1", "Custom.Owner.2", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ID", "Count"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.ID", "Custom.Role", "Custom.Owner.1", "Custom.Index", "Custom.Owner.2"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Owner Plus Index", each [Custom.Owner.1] & Number.ToText([Custom.Index])),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Custom.ID", "Custom.Role", "Custom.Owner.1", "Custom.Index", "Owner Plus Index", "Custom.Owner.2"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"[#"Owner Plus Index"]), "Owner Plus Index", "Custom.Owner.2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"}),
#"Filled Up" = Table.FillUp(#"Removed Columns1",{"Owner2"}),
#"Filled Down" = Table.FillDown(#"Filled Up",{"Owner1"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down", {"Owner1"})
in
#"Removed Duplicates"

Hope this helps!

Peter