Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sourcing the same dataset from multiple tabs

Greetings,   We are using PowerBI to represent data from multiple company Quickbooks files. This example is for pulling data for uncleared checks across company files. Below is a sample of the data...
swise001's avatar
swise001
Continued Contributor
6 years ago

Anonymous 

 

When you import Excel files into Power BI - you are provided with a 'navigation' pane that allows you to select more than 1 sheet at a time: 

If you select all 3 sheets - Power BI will import all 3 sheets as separate queries (hit "Transform Data")

 

At this point - you'll have to do some 'clean-up' in Power Query - in order to bring all 3 sheets into the same structure.  Power Query is more than capable of handling this kind of work (based on your images).  Once you have all 3 tabs structured the same way - you can combine them into 1 unified table and import that into your Power BI model. 

 

If you expect the names of the 'tabs' and the structure of the company exports to remain the same over time - then this import method will be reusable as the excel files update.  However, if the number of tabs and/or structure of the Excel tables will tend to change every time you run an export - then the imports to Power BI will fail when Power BI tries to follow an import path that no longer matches the way the files were when it was created.   

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you very much for your direction on this.

     

    How do I merge all common data info 1 unified table? I will have all excel tabs in the same format ultimately, the only difference being the company that the excel tab belongs to.

     

    Kind regards

     

    Mick