Forum Discussion

TorienDeJager's avatar
TorienDeJager
Frequent Visitor
2 years ago

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...
Need Help
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
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's avatar
    TorienDeJager
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.