locked
Power Query load from table for all worksheets RRS feed

  • Question

  • Hello. How do I do a load to connection from ten worksheets without having to repeat the process for each worksheet? Gracias. 
    Monday, August 15, 2016 8:31 PM

Answers

  • Hi Alex,

    If all the worksheets are in the same excel file, then you can load it into PowerPivot data model together.

    If those worksheets located in different excel file, you need to load the worksheets one by one.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Sunday, August 28, 2016 7:26 AM
    • Marked as answer by Charlie Liao Sunday, September 4, 2016 9:23 AM
    Thursday, August 18, 2016 6:17 AM
  • Hello. Yes, I meant from powerpivot in the same workbook.  @ Charlie: How were you able to get all those tables to come up? Ah, I think I got it, can you select the workbook that you are working from or does it have to be a seperate workbook? 

    Hi Alex,

    When importing excel data into PowerPivot data model, the excel file should not be in use. So you need a separate workbook.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Sunday, September 4, 2016 9:24 AM
    Sunday, August 28, 2016 7:25 AM

All replies

  • Do you mean you want to combine the data in the ten worksheets into one table? Power Query is the ideal tool to do this, using a combination of reading a file list from a folder and having a parameterized query to load data from one worksheet. Provided that the structure of the worksheets is the same.

    I can elaborate if this is what you're looking for, but I'm pretty sure the answer is somewhere in the Power Query forum.

    • Proposed as answer by Charlie Liao Sunday, August 28, 2016 7:26 AM
    Tuesday, August 16, 2016 7:00 AM
    Answerer
  • Hi Alex,

    If all the worksheets are in the same excel file, then you can load it into PowerPivot data model together.

    If those worksheets located in different excel file, you need to load the worksheets one by one.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Sunday, August 28, 2016 7:26 AM
    • Marked as answer by Charlie Liao Sunday, September 4, 2016 9:23 AM
    Thursday, August 18, 2016 6:17 AM
  • Hello. Yes, I meant from powerpivot in the same workbook.  @ Charlie: How were you able to get all those tables to come up? Ah, I think I got it, can you select the workbook that you are working from or does it have to be a seperate workbook? 
    Friday, August 19, 2016 10:51 PM
  • Hello. Yes, I meant from powerpivot in the same workbook.  @ Charlie: How were you able to get all those tables to come up? Ah, I think I got it, can you select the workbook that you are working from or does it have to be a seperate workbook? 

    Hi Alex,

    When importing excel data into PowerPivot data model, the excel file should not be in use. So you need a separate workbook.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Sunday, September 4, 2016 9:24 AM
    Sunday, August 28, 2016 7:25 AM
  • Got it. Thank you. 
    Monday, September 12, 2016 6:05 PM