Splitting a Table into several tables based on the value of a column

[gtamir]

Same question as: 

http://community.powerbi.com/t5/Desktop/Splitting-a-Table-into-two-based-on-the-value-of-a-column/td...

But what if I have a table combining data from 50 shops and I want to split it to a different table per shop.

[foxyox89]

Hi,

This may not be a solution in PowerQuery as such but you could use a simple Python script to split a csv based on a value in a  column (we do this) you could then maybe import all csvs into PowerQuery by importing from folder?

I've not tried this myself.

Let me know if you need the Python script I'll point you in the right direction.

[Greg_Deckler]

Interested in the use case for that. But I would just do that in the query editor and just copy and paste your query and change your filter.

[gtamir]

I am not sure I understood what I should do.

[Greg_Deckler · Accepted solution]

OK, assuming you have a query that is importing your data, in the Query Editor, filter that query to just the data for "shop 1". Right click the query and choose "Copy". Paste the query. Change the filter to be "shop 2". Rinse and repeat 48 more times.

[alwweb]

@Greg_Deckler , thank you for posting that.  

My use case is playing with the Bing COVID dataset.  It has both aggregates and details in the same table (source csv), making a lot of extra work filtering on the visualization side to get correct numbers.  By splitting it out in Power Query, it can be done once rather than for every visualization.

I'm curious from a performance and best practice standpoint, should I duplicate or reference the initial query that was imported from the csv?

Also, does filtering actually remove the rows from the file to keep the size under control better, or is it only hiding them?

[Greg_Deckler]

You want to reference as my understanding is that it then only loads the data once instead of every time for every query.

[Nizamuddin]

Hello,

I need to split the rows based on the column value, is there any way to split or still I have to use the filter option? The resaon for asking this question is I have n number of dataset, it will be a pain to do filter option each time. I wanted to know is there any DAX/Script available to perform this repeated task. Please have a look to the screen shot below. Hope this make sense, please do contact me if you need further details about this.

[Anonymous]

I am facing the same problem. Have anyone a solution? 

[gtamir]

Yes, this is a bit of work but possible. Thanks