Forum Discussion
Work with multiple worksheets in PBI
Hello all,
I am kinda new to Power BI and I am looking to import an Excel file with multiple worksheets (all of them have the same data structure/headers). This excel file is related to production scheduling and each worksheet represents a different equipment. Each sheet would be updated daily.
The thing is if I import all the worksheets i am going to have lots of different Queries using same field names and the report I need to build will use all the sheets at once but the same field (Order Number by instance). Example: get start/end date for all orders per equipment (which is divided in in multiple worksheets)
So how do I do this without messing with the Excel file? User will change the data tab by tab and my report must reflect all the changes at once.
Not sure if its clear or not, but thanks anyway
If you are saying that you want all the tabs to show up together in Power BI, load them in Power Query then append them together as new query and disable the load on each of the individual files, this way when you are back in Power BI you have one appended table to work with.
Yes it will. Every time the source file gets updated and Power BI refreshes it runs through every step in Power Query - in your case it would involve the individual files coming in and then getting appended. When you turn off enable load in Power Query that just means that that individual query won't load in Power BI as a separate table, basically, you are hiding them. They will refresh and the Appended query will do it's thing 🙂 Managing query refresh - Power BI | Microsoft Learn
5 Replies
- audreygerred
Super User
If you are saying that you want all the tabs to show up together in Power BI, load them in Power Query then append them together as new query and disable the load on each of the individual files, this way when you are back in Power BI you have one appended table to work with.
The problem with that when I update my source file my appended query will not be updated
- audreygerred
Yes it will. Every time the source file gets updated and Power BI refreshes it runs through every step in Power Query - in your case it would involve the individual files coming in and then getting appended. When you turn off enable load in Power Query that just means that that individual query won't load in Power BI as a separate table, basically, you are hiding them. They will refresh and the Appended query will do it's thing 🙂 Managing query refresh - Power BI | Microsoft Learn
