Forum Discussion

dngilliam's avatar
dngilliam
New Member
5 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

5 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    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's avatar
      dngilliam
      New Member
      5 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.