How to build one Power BI dashboard from multiple Excel workbooth with DIFFERENT data structures

[dngilliam]

Hello,

I have multiple Excel workbooks that are not identical to each other. Each workbook tracks a different set of data by different team members. However, as a team leader, I want the ability to pull their data together and display one dashboard that is segmented into their respective projects/duties.

I saved each Excel workbook file in SharePoint Online hoping to build a query in Power BI to pull that data. It worked for the first workbook file, but when I added other workbooks, I received errors after the 'invoke custom function' feature because the column headers/data structure varied.

So far, I've only come across articles and videos of loading multiple Excel workbooks that have identical data structures. I want to feed each Excel workbook of data from each team member into Power BI and display a one-paged dashboard. Ideally, each team member would update their monthly data into their respective Excel workbook and I can refresh Power BI to generate a report for the current month.

Is this possible? If so, are there multiple options to achieve this? I'm using a government-licensed edition of Power BI Desktop so there's also a chance that I don't have the full functionality of the tool yet.

[richbenmintz]

Hi @dngilliam ,

Are you adding each file as seperate tables or are you trying to import them all with the same query?

I would suggest importing each file as a query, transform the data into a conformed schema then append the queries together.

Hope that helps

[dngilliam]

Hi @richbenmintz ,

I tried to import them with the same query but of course received an error message because each file had different column headers. So, I assumed that I could copy the orginal query and modify the filename and a few minor elements in the code, but it  failed and that's where I'm stuck.

Is there a clear article/video that can walk me through those steps? I'm very new to Power BI.

[v-xiaoyan-msft · Accepted solution]

Hi @dngilliam 

We see that your operation is failed because of errors reported due to column headings/data structures varied.

To solve this problem, you may follow the steps in the following article: Merge Files With Different Column Headers

This article explains what happens when we try to merge files with different column headings.

For further more, please see if these articles can help you: Import Multiple Files Containing Multiple Sheets with Power Query

Also, here is a YouTube video here that may be able to help you .

Hope it helps,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

if the structure of the columns is not the same, you can import them individually, change the column headers in Power Query and finally append the tables to create a single fact table

[PC2790]

It would be better if all the excel files have the same structure.

That way you will be able to combine all of them using a single custom function.

To maintain uniformity, you can create a single excel, limiting the fields by putting proper validation so that your team doesn't hamper with the structure of the report.