Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combing thousands of excel files using Power Query. Is my workflow correct?

Hi,    I've built a power query function to extract data from excel files. The excel files are in the same format. The files are stored on a Onedrive folder. I've successfully queried the data usin...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    exactly, I only added Table.Buffer

    So give it a test and let us know

     

    Jimmy

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

Hello Anonymous 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Jimmy801 ,

 

Thanks for the follow up. 

 

My code has changed a bit since I last posted, but wanted to confirm that the only change was adding "table.buffer" at the following section:

 

Daily_Data_Entry_Sheet = Table.Buffer(Source{[Item="Daily_Data_Entry",Kind="Sheet"]}[Data]),

 

if so, i can test it on my current query function.

 

Thanks again,

 

Tony

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello Anonymous 

     

    exactly, I only added Table.Buffer

    So give it a test and let us know

     

    Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Jimmy801 

       

      Sorry for the late reply on this, but I was finally able to do a little testing on this. 

       

      I ended up just directly querying the files into power bi and skipping the excel step. I was having trouble setting up auto refresh of the data connection in excel (i wanted to not have to open the file and hit refresh). I copied the powery query function into my PBI and rebuilt the connection. I then buit two different versions with one with the table.buffer and then uploaded it to the pbi service and then setup auto refresh.

       

      Interestingly, it appears to take a little longer. Both refresh was scheduled for 11:00pm.

       

       

      Thanks.

      • Jimmy801's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hello Anonymous 

         

        was the initially request, do combine thousands of Excel-files, fullfilled?

         

        Hello

        have you been able to solve the problem with the replies given?
        If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        All the best

        Jimmy