Splitting one table into 2 in Power Query?

Results 1 to 4 of 4

Thread: Splitting one table into 2 in Power Query?

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Splitting one table into 2 in Power Query?


    Splitting one table into 2 in Power Query?
    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?
    Last edited by jbaich; Nov 30th, 2017 at 06:06 PM.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting one table into 2 in Power Query?

    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.
    Matt Allington is a Professional Power BI consultant, trainer and Microsoft MVP based in Sydney Australia at http://Exceleratorbi.com.au.
    Author of the book Learn to Write DAX
    What is Power Pivot?

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting one table into 2 in Power Query?

    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!

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting one table into 2 in Power Query?

      
    Quote Originally Posted by jbaich View Post
    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.
    Matt Allington is a Professional Power BI consultant, trainer and Microsoft MVP based in Sydney Australia at http://Exceleratorbi.com.au.
    Author of the book Learn to Write DAX
    What is Power Pivot?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com