Splitting one table into 2 in Power Query?

jbaich

Board Regular
Joined
Nov 2, 2011
Messages
139
Hi all, brand new to power query, been watching lots of tutorials over the last couple days and I feel like I've almost accomplished what I set to do, but I'm stuck just before the end! hopefully someone here can push me across the finish line :)

The goal was to take one big ugly data dump that is effectively 2 nested tables and try to extract them into 2 individual tables. I've gotten to the point where I can now group the records according to which table they should be in, but I can't figure out how to load them as separate tables?

1T62H0

1T62H0


So right now, i can see my two groups and when I open up the associated tables all looks as it should be (minus a lot of formatting and clean up), but I don't know how to convert or keep each grouping as it's own new independent table, which i'd like to load into the data model... any suggestions? can I split the query here into 2 new queries or something?
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You can create a query, then set it to “do not load”. In Excel, you have to set “load to” to be “only create connection” and “don’t load to excel”. Then in the query editor, right click on the table and select reference. This will create a new query pointing to the original, then reshape to your needs. Repeat for the second table.
 
Upvote 0
Thanks Matt! so, if i'd previously already loaded into a worksheet, does that mean i should rebuild the query from scratch? I see "Close and Load To" is no longer an option... I tried right clicking on the table and i see what you mean about it starting a new query, so I've gone and finished all the query editor work i had left for each table and am left with 2 separate queries, which is great! Thank You!!!

It looks like when I open the data model manager, I have one table that is linked (chain link icon) and one that doesn't... presumably that's the one where i selected to add to the data model from Power Query... I'm still not 100% clear on what the difference is... should they both be linked or unlinked? does it make a difference?

Thanks again!
 
Upvote 0
I see "Close and Load To" is no longer an option...

If you go into Excel, show the Query Pane on the right, right click on the query you no longer want to load to Excel, then select "load to" from there, you can set it so it doesn't load.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top