# Splitting one table into 2 in Power Query?



## jbaich (Nov 30, 2017)

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?











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?


----------



## Matt Allington (Nov 30, 2017)

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.


----------



## jbaich (Nov 30, 2017)

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!


----------



## Matt Allington (Dec 1, 2017)

jbaich said:


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


----------

