Excel - Power Query How to combine multiple tables using a combination of Append and Merge

[TorienDeJager]

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/tables into a single file to publish to my ecommerce platform. I need to do this based on a single column (lets call it Variant SKU). Example: 

There are 1000's of SKU's with various costs/values, etc. There can also be duplicate SKU's. 

Where i am stuck: If I append the tables, I get duplicate rows of SKU's. And I can't Merge the tables as I have more than 2 tables. 

The Outcome I seek: Is to have a single table, with a list of SKU's and where duplicate SKU's are found, additional columns with the variables (i.e Cost price, Sell Price, etc). 

Any help would be greatly apprecaited, as I am very new to this.

[Syndicate_Admin]

Desired output screenshot is not readable.

[Syndicate_Admin]

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.

[TorienDeJager]

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

[Anonymous]

Hi @TorienDeJager 

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.