Jul 26 2021 01:14 PM - edited Jul 26 2021 01:56 PM
I have a document with 24 tabs, each has different sales data about products.
I want to combine all of these products into one master list, with the duplicates removed. I only want to make the list consist of columns "(Parent) ASIN" and "Title" (columns A and B in the sheets).
I understand I could copy paste and remove duplicates, but I want to be able to plug in any 24 sheets (we do this for many companies) and have a formula that automatically creates a master list. What is the best way to do this?
I was told by Microsoft support that this might entail using PIVOT or a master list function? Open to any and all solutions.
Jul 27 2021 12:16 AM - edited Jul 27 2021 01:18 AM
@btillis94 And this is how. (Workbook with PQ attached)
Edit: Uploaded the wrong file initially. Then realized that it connected to a file on my own system. Better to upload the M-code that you can paste into the Advanced Editor. Can't add it here, so refer to my next post. Just change the path name to the file.
Jul 27 2021 01:20 AM
let
Source = Excel.Workbook(File.Contents(".............\Desktop\Summary2.xlsx"), true, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Item] <> "Summary") and ([Kind] = "Sheet")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"(Parent) ASIN", "Title"}, {"(Parent) ASIN", "Title"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Data", {"(Parent) ASIN"}),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"(Parent) ASIN", Order.Ascending}})
in
#"Sorted Rows"
May 31 2022 01:15 AM
May 31 2022 01:39 AM
@redriverbluehill Not sure I follow, but if you connect to a file with Power Query to a file like in the example file that was attached to the original post, you select one of the sheets to transform. Then, in the applied steps list Source and Navigation will show, where Source has the correct path the file on your server. Delete the Navigation step so that you are left with the content of the entire workbook.
Now you can filter for all the sheets you need/want from the Kind column, and expand the Data column. And that's basically what the code in my answer does.