Forum Discussion

TorienDeJager's avatar
TorienDeJager
Copper Contributor
Nov 22, 2023

Merging and appending multiple files using the Data Tab

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

 

Here is a simple example (but remember that duplicates can be in several tables), I am only showing 2:

Lets say I have the same field (SKU) for 3 products, where the price/other columns may contain different 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 :facepalm:

 

7 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hi TorienDeJager 

     

    I am sure that this must be simple

    When you Searched the Net :cool: for same/similar scenario you probably understood that's not that simple :unamused:. Depending on the actual scenario & data sources approach might be slightly different

     

    Quick (first) questions:

    1. Can duplicate SKUs exist within the same Table?
    2. When duplicated SKUs exist in 2, 3, ... 7 Tables, do they share/have the same [PRODUCT_ID] (you show [PRODUCT_ID] in your desired output but not in your sample Table)
    3. Could you upload & share (i.e. with OneDrive, Google Drive,... or the like) a workbook with says 3 (out of your 7) Tables so we don't spend time creating dummy - and probably not representative - data please?
    • TorienDeJager's avatar
      TorienDeJager
      Copper Contributor
      1. Duplicate SKU's can't exist in the same table.
      2. The product ID is Unique for every line item, and is typically allocated within the ecommerce platform on upload.
      3. Unfortunately I can't break the file down, as it has taken on a life of it's own over the month, with many hundreds of dependencies.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        TorienDeJager 

         

        1. Duplicate SKU's can't exist in the same table
          OK
        2. The product ID is Unique for every line item
          That doesn't help me understand I'm afraid. Does 'line item' mean SKU?
        3. Unfortunately I can't break the file down
          ??? In an effort to help you I created 3 tables (attached) with dummy data (took +/- 15 mins, no more...). Are they representative of your scenario/context? If not please reshape, fill them & share the file so we have something realistic to work with. Thanks

Resources