Split table into multiple tables based on value of 2 columns

[donpep0]

Hi!

I'm working with Power Query and I need to split the main table into multiple tables. It's possible to split a table into multiple tables based on 2 value conditions?

I have the below table and I need to create multiple tables based on Product and weekday

Product	Date	Weekdate	#Weekdate	Volume
ID001	7/15/2022	Fri	5	132
ID001	7/16/2022	Sat	6	413
ID001	7/17/2022	Sun	7	124
ID001	7/18/2022	Mon	1	124
ID001	07/01/2022	Fri	5	124
ID001	07/04/2022	Mon	1	125
ID002	7/15/2022	Fri	5	523
ID003	7/15/2022	Fri	5	324
ID0003	7/16/2022	Sat	6	235
ID0003	7/18/2022	Mon	1	523

And the expected outputs look like this:

Table1
Product	Date	Weekdate	#Weekdate	Volume
ID001	7/18/2022	Mon	1	124
ID001	07/04/2022	Mon	1	125
Table2
Product	Date	Weekdate	#Weekdate	Volume
ID001	7/15/2022	Fri	5	132
ID001	07/01/2022	Fri	5	124
Table3
Product	Date	Weekdate	#Weekdate	Volume
ID001	7/16/2022	Sat	6	413
Table4
ID001	7/17/2022	Sun	7	124
Table5
ID002	7/15/2022	Fri	5	523

Do you know how can I do it? I have multiple products for each day

[vladimirskpu]

I have the same question but I think the answer will be the same: it is not possible with Power Query. My reason to split table into more tables: within one table there are records which I would like to process differently: I want to assign to the table record specific identification ID based on certain criterions (conditions) in levels of conditions: first, based on most specific condition, bridge (for merge) then if not found in the most specific bridge based on different field, less specific bridge - put into ID some average substitute ID and then third possible way if neither of the two is applicable (both merges was lef outer join) use very general substitute ID.... So I think I have to create duplicates of the whole table, or, should be better(??), reference to the original table, filter it twice and at the end append each part of such adjusted table together... The only issue with that approach is that the table is quite large so it takes more time to run all three parts of processing and then to process final resulat as one table... I think for such a task there are better tools than power query (knime e.g.) but still even this task is achievable with Power Query, just less efficient...

[v-jingzhang]

Hi @donpep0 

Power Query doesn't support to split a query into multiple queries based on values. For a better modeling and report authoring experience, it is not recommended to split same structure data into multiple queries too. 

You can try the Group By feature to get a similar result in one single query as below. After grouping, you will have a table column. Every cell in this column is a table that only has data of this product-weekdate group. You can choose to expand the table column then every goup data will be together. Grouping or summarizing rows - Power Query | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[BA_Pete]

Hi @donpep0 ,

Looks like an XY Problem to me.

Can you give details on what exactly your end-goal is please? Splitting tables into pieces like this is highly irregular, so I'm thinking there's probably a faster/easier way to get to your end-goal than this.

Pete