none
Power Query - Recursively Load Excel Worksheets RRS feed

  • Question

  • I have an Office 365 account and I am using OneDrive to share data with multiple companies. Under each company there are multiple operating units. Under the operating unit there are multiple excel workbooks. Inside each workbook are multiple worksheets with tables. I want to link to each worksheet/table and develop a Power BI model.

    Is it possible to recursively (with code) power query each worksheet? I can power query each worksheet manually but there are over 2,000 worksheets that need to be linked or queried to run my data model.

    Any advice is greatly appreciated.

    Thursday, May 7, 2015 6:30 PM

Answers

  • Great, so you found the key already :-)

    This should work very well with Chris’ solution (Content as binary is just as it should be).

    So only need is to replace his line 15 with this:

    Source = SharePoint.Files(#"Directory containing Excel files to combine"),


    Imke

    Friday, May 8, 2015 5:39 AM
    Moderator
  • Well, actually you're more optimistic than I then. Tried to reproduce but failed.

    Seems I shouldn't have been able at the first time because I'm using a non-english SharePoint-site that doesn't support "SharePoint.Files/Table" Access yet. Sorry.

    But from what I understand there might be credentials needed. So in the procedure you've described above when you switched .Tables to .Files (and saw all your files) - have a look at that query and check the code for the credentials - this would probably need to be inserted as well.


    Imke

    Monday, May 11, 2015 7:40 PM
    Moderator

All replies

  • Yes you and programmatically refresh Power Query queries using VBA. The following link looks helpful; -

    http://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Thursday, May 7, 2015 8:05 PM
  • Thursday, May 7, 2015 8:06 PM
    Moderator
  • Thank you for sharing the links -

    I found the one by Chris Webb earlier and I can get that to work very well when pointing to my local hard drive.  I can't get it to work when I point it my SharePoint list.

    Part of my confusion is that when I initially point to the SharePoint list it returns it as

    Source = SharePoint.Tables

    and I have to change it to

    Source = SharePoint.Files

    From here I can see all the xlsx files that exist on my SharePoint site but they are listed as Binary. It is inside each of these xlsx files that the specific worksheets exist that I need to merge together.

    I can't figure out what I need to change in the string to make it work with what Chris did.

    thank you very much

    Thursday, May 7, 2015 8:52 PM
  • Great, so you found the key already :-)

    This should work very well with Chris’ solution (Content as binary is just as it should be).

    So only need is to replace his line 15 with this:

    Source = SharePoint.Files(#"Directory containing Excel files to combine"),


    Imke

    Friday, May 8, 2015 5:39 AM
    Moderator
  • thank you again.  I have been working with it this morning and I changed line 15. It returns saying the tables are blank.  I think I also need to a change another line but I can't seem to put my finger on it yet.  I'll keep working but I agree we are on the right track.

    Wag80

    Monday, May 11, 2015 4:22 PM
  • Well, actually you're more optimistic than I then. Tried to reproduce but failed.

    Seems I shouldn't have been able at the first time because I'm using a non-english SharePoint-site that doesn't support "SharePoint.Files/Table" Access yet. Sorry.

    But from what I understand there might be credentials needed. So in the procedure you've described above when you switched .Tables to .Files (and saw all your files) - have a look at that query and check the code for the credentials - this would probably need to be inserted as well.


    Imke

    Monday, May 11, 2015 7:40 PM
    Moderator
  • Hi Wag80,

    Have you been able to resolve this issue?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, May 29, 2015 11:48 AM
    Moderator