cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gtamir
Post Patron
Post Patron

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

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.

1 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

I am not sure I understood what I should do.

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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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?

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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.

 

Dataset.JPG

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

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors