Forum Discussion
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 we are able to export to Excel. As you can see in screenshot 1 and for the tab “Company 1” we have a company file with multiple bank accounts. In screenshots 2 and 3 for tabs “Company 2” and “Company 3” respectively, these companies each only have 1 bank account so the data exported looks a little different than Company 1’s export. We would like Power BI to pull data from each tab to represent a global snapshot and global total for all checks that have not cleared the bank yet. Is this possible to do and have Power BI pull from multiple tabs? Or would we want a separate Excel file for each tab and a separate PowerBI file to pull from each file, if we went that route, is it possible to have PowerBI give some sort of total for all companies? Another thought we had was possibly doing a workbook like the sample photos below, where we have a tab for each company file, but then create a tab which would pull all tabs into a single one. The issue with that is the tabs would all change each time they are updated as far as how many rows worth of data (uncleared checks) they have, making it difficult to create formulas in a consolidated tab with the data set ranges (number of rows) changing each time we update the workbook. Any help or guidance on this would be greatly appreciated.
Many thanks
Mick
Screenshot 1
Screenshot 2
Screenshot 3
4 Replies
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.
When you select excel as the data source you will be prompted to select which tab or tabs in the selected excel file you want to import. This will give you each tab as an individual query which you can then transform to match the correct format before you finally append the queries together.
But I believe that the first step is to just import the 3 different tabs. It's quickly done and will give you a better picture of what needs to be done to reach the end-goal.
Br,
JHi Anonymous,
#1, You can get data from different excel workbook sheets as different query tables, it should split one file to multiple tables.
#2, You can use 'fill down' feature on the header column to fill the blank row with correspond category values, then you can add filters to remove records who not have detail rows contents to filter subtotal and grand total rows.
How to Fill Down Blank or Null Cells in Power Query
Regards,
Xiaoxin Sheng
