Forum Discussion
Creating a master list from many lists
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 lis...
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"
hi Riny_van_Eekelen - i think i need the same function. but does your formula mean that if i move the file to another location i need to update the code? this code depends on the file staying in one place on the server?
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.