cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rossmon
New Member

Removing 1st column duplicates but keeping the information.

This seems kind of unique, but may not be.  I'm in a new role and learning Power BI.

I have a table of information coming from a dataflow set to automatically update.  I need to use this as a dimension table, however some items have multiple entries (because of multiple accessories) and I need to clean the data in order to reach a one to many relationship.

Basically I am looking to have the 2nd and 3rd Accessory feed into additional columns.  

 

To simplify, this is the start:

ItemAccessory
Item 1Accessory 1A
Item 2Accessory 2A
Item 2Accessory 2B
Item 3Accessory 3A
Item 3Accessory 3B
Item 3Accessory 3C
Item 4Accessory 4A
Item 5Accessory 5A
Item 5 Accessory 5B
Item 6Accessory 6A

 

and this is my desired result:

Item1st Acc2nd Acc3rd Acc
Item 1Accessory 1A  
Item 2Accessory 2AAccessory 2B 
Item 3Accessory 3AAccessory 3BAccessory 3C
Item 4Accessory 4A  
Item 5Accessory 5AAccessory 5B 
Item 6Accessory 6A  

 

 

Thanks for the help in advance!

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRckxOTi0uzi+qVDB0VIrVgcoYocgY4ZZxQsgYo8gYO+KUwa3HGSFjgiJjgmSaKYqMKW4ZJHvMUGTMgHpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Accessory = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Item"}, {{"ar", each Table.Transpose(Table.SelectColumns(_, {"Accessory"}))}}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Column1", "Column2", "Column3"}, {"Acc1", "Acc2", "Acc3"})
in
    #"Expanded ar"

Screenshot 2021-06-09 145719.png

View solution in original post

1 REPLY 1
CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRckxOTi0uzi+qVDB0VIrVgcoYocgY4ZZxQsgYo8gYO+KUwa3HGSFjgiJjgmSaKYqMKW4ZJHvMUGTMgHpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Accessory = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Item"}, {{"ar", each Table.Transpose(Table.SelectColumns(_, {"Accessory"}))}}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Column1", "Column2", "Column3"}, {"Acc1", "Acc2", "Acc3"})
in
    #"Expanded ar"

Screenshot 2021-06-09 145719.png

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors