July 6, 2020
Is it possible to split a table into separate tables based on each distinct value in 2 columns?
sn customer purchase
0 john bag
1 mary shoe
2 david bag
I want to power query to auto split based on sn and customer. so the above would create 3 tables with each row (as they are all unique based on sn and customer) and load into 3 worksheets.
November 8, 2013
PQ is designed to aggregate data, rather than splitting data.
Queries cannot replicate themselves to load a different data set into a different sheet.
An alternative is to not load data to sheets, build a pivot table with that connection, add customer in the Filter section of the pivot, then use the Show Report pages option. Excel will automatically create new sheets for each customer (named after customer), each sheet will have the pivot table filtered for that customer.