Forum Discussion
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
7 Replies
- LorenzoSilver Contributor
I am sure that this must be simple
When you Searched the Net
for same/similar scenario you probably understood that's not that simple
. Depending on the actual scenario & data sources approach might be slightly different
Quick (first) questions:
- Can duplicate SKUs exist within the same Table?
- 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)
- 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?
- TorienDeJagerCopper Contributor1. 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.- LorenzoSilver Contributor
- Duplicate SKU's can't exist in the same table
OK - The product ID is Unique for every line item
That doesn't help me understand I'm afraid. Does 'line item' mean SKU? - 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
- Duplicate SKU's can't exist in the same table