Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Desired output screenshot is not readable.
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.