Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
foxyox89
New Member

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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Anonymous
Not applicable

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.