Combine multiple xlsx files into a single single field for data & reporting

[DJPoloMan]

I have searched the forum and while I am sure a solution exist I could not find one. Please help direct me to another thread if there is a solution I can use for this question.

I have a xlsx files for each calendar month of the year which contains daily sales records by customer & material number. Therefore the same customer and material number will be listed multiple times throughout the given month. The headers are always following the same structure in each file. Unfortunately, I have exceeded the lines allowed for each tab otherwise I would just create a single tab containing all the months data.

How can I upload these files and combine them so I can create dashboards using a single source and not have to reference multiple excel tabs or files? I have provided a screen shot giving an example of what the excel files look like.

I would also like to structure this so I can just upload these files in the upcoming months and have the PowerBI dashboard updated after refreshing.

Thank you for your help and time.

[FlaF]

Hi,

I think a solution can be upload your files excel in power bi and then you can combine your xlxs files, in edit query, with "append queries" in home menu. 

You can upload new xlxs files in power bi for the upcoming months and append them too

I hope it is the right way to solve your problem

[v-xjiin-msft]

Hi @DJPoloMan,

If these xlsx files have relationships among each other. I think you can try Merge or Append in Edit Queries in Power BI.

Reference: https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

Thanks,
Xi Jin.

[Anonymous]

You've mentioned in your post that you have reach some sort of upper limit per tab.  I would suggest considering an SQL database and find a method to take your information and get it stored in that database.  That would make your single source desire much easier to achieve.

[DJPoloMan]

I appreciate your response and believe this would be the ideal long-term solution but I am interested in understanding if there is some kind of temporary solution in place I can use.