Forum Discussion
Excel - Power Query How to combine multiple tables using a combination of Append and Merge
Use Case: I receive 7 data files (csv and Excel), from various suppliers, in various formats. I clean the data through Power Query into the same column headers. I now need to combine these files/tabl...
Where i am stuck: If I append the tables, I get duplicate rows of SKU's. You can add a deduplication step in Power Query if needed, or you can handle the duplicates in Power BI.
I tried the duplicate function in Power Query, however it removed 27000 records, with no explanation 🙂
Lets say I have the same field (SKU) for 3 products, where the price/other columns may contain differant values, I need to be able to append those values to the product SKU.
Example:
Desired Output:
I am sure that this must be simple. 🙄
You can create a blank query and put the following code to advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZC7CsMwDEX/RXMGKbbzGNsOXUszGg+FeislpP8PzVW44Ha5CB8dSyhnMenkdL6oorB5j6B7XLda31K6LL03LKooDIGHdX3Vj/PAD5xPB7/Vx+Y00kabjQd1kugFziQZ6EBOrTPSAYkNmKikZkknM5X0v4ApEQZaS36O0kOKiHt9Silf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, SKU = _t, #"Cost Price" = _t, #"Sell Price" = _t, De = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Min(Table.SelectRows(Source,(x)=>x[SKU]=[SKU])[Product ID])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Product ID]=[Custom] then Table.ToList(Table.SelectColumns(Table.SelectRows(#"Added Custom",(x)=>x[SKU]=[SKU] and x[Product ID]<>[Product ID]),{"Product ID","Cost Price","Sell Price","De"})) else null), #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom.1", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product ID", Int64.Type}, {"SKU", type text}, {"Cost Price", Int64.Type}, {"Sell Price", Int64.Type}, {"De", type text}, {"Custom.1.1", Int64.Type}, {"Custom.1.2", Int64.Type}, {"Custom.1.3", Int64.Type}, {"Custom.1.4", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom.1.1", "RelatedID"}, {"Custom.1.2", "RelatedCost"}, {"Custom.1.3", "RelatedProce"}, {"Custom.1.4", "RelatedDe"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Custom", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom]=[Product ID])), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.