Forum Discussion

emma313823's avatar
emma313823
Helper V
5 years ago
Solved

Combining multiple files

Hi All

 

I've been using Microsoft Access kind of like a data repository for yearly files which I use append queries to pushed into a combined table in Access. It puts my data at more than a million rows. I then connect Power BI Desktop in order to do the visuals I have on my dashboard. I've run into a major issue in the last couple of days where for whatever reason the append queries I had in Access no longer work after more than 2 years of using them.

 

The problem I have is that my historical yearly files do have changes that may take effect if a salesperson changes an account, I need to keep those updated. While I'd love to leave the historical files as locked due to salesperson changes each month, I must always overwrite the historical data to keep it fully updated.

 

I came across this article and am wondering if this would work for me. If I understand what I'm reading, this would connect to my worksheets and combine the data. What I'd like to better understand is all my files are in one folder, the ordinal position is the same as are header row names. If each month a salesperson changes in various yearly files...will using this option from the details of the article also reflect those changes in my excel files once I refresh my data in Power BI Desktop?

 

Emma

 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries

Need Help

3 Replies

  • mhossain's avatar
    mhossain
    Solution Sage
    5 years ago

    Hello emma313823 

     

    Yes, this will work, you can keep all of your files in a folder and powerbi will combine all the files, please note it will always refresh all the files.

    Header names must be same in all the files, if you see differences of column numbers in different file, you can keep one extra temp file having all the field names and while importing and combining consider this temp file as your template file and select.

    As you have already mentioned, historical excel files will also get changed, so combining from the folder is good option.

    Hope this is making sense.

    • emma313823's avatar
      emma313823
      Helper V
      5 years ago

      Thanks for this info.

       

      Further on this query. Would you be able to share some insight on the following?

       

      As 2021 progresses, due to row limitations in Excel I will have to drop extrapolate 2018 data into an individual file. I would drop that 2018 file into the same folder where all my other files reside. If what you previously confirmed is true...then refreshing the dashboard as it is would remove all of 2018 data because I removed it into its own file. Would that be correct?

       

      How would I then incorporate that individual year file based on the instructions? Do I do nothing because my connection is to the folder? Will BI Desktop automatically grab that 2018 individual file and incorporate it? Or do I need to do something beyond what was originally done?

       

      Emma