Showing results for 
Search instead for 
Did you mean: 
Yarroo Frequent Visitor
Frequent Visitor

Power BI loading unwanted worksheets on refresh

I'm struggling to understand why Power BI reads uwanted worksheets from Excel.


In Navigation step in query editor I select "MSTN MATERIALS", but then when I refresh, I see messages in the right bottom corner mentioning different "worksheets" with "$":











How to "force" Power BI to load ONLY the selected sheet "MSTN MATERIAlS" in this case and skip any other worksheets in the workbook?


2018-11-23 11_42_15-RPM_Dashboard_Foods - Power Query Editor.png




2018-11-23 11_42_19-RPM_Dashboard_Foods - Power Query Editor.png



2018-11-23 11_42_21-RPM_Dashboard_Foods - Power Query Editor.png




2018-11-23 11_42_24-RPM_Dashboard_Foods - Power Query Editor.png




2018-11-23 11_42_31-RPM_Dashboard_Foods - Power Query Editor.png




2018-11-23 11_42_38-RPM_Dashboard_Foods - Power Query Editor.png





2018-11-23 11_43_22-RPM_Dashboard_Foods - Power Query Editor.png



2018-11-23 11_43_50-RPM_Dashboard_Foods - Power Query Editor.png



 2018-11-23 11_42_45-RPM_Dashboard_Foods - Power Query Editor.png


2018-11-23 11_42_59-RPM_Dashboard_Foods - Power Query Editor.png



2018-11-23 11_43_37-RPM_Dashboard_Foods - Power Query Editor.png

ncbshiva Member

Re: Power BI loading unwanted worksheets on refresh

Just right click on the unwanted query and disable the load and report refresh .

Yarroo Frequent Visitor
Frequent Visitor

Re: Power BI loading unwanted worksheets on refresh

It's not an unwanted query, it's a part of one query, which connects to Excel workbook.


my query looks like:


    Source = Excel.Workbook(#"Sample File Parameter16", null, true),
    #"MSTN Materials1" = Source{[Name="MSTN Materials"]}[Data],

So I simply take one Workbook (which contains many worksheets) and in step 'Navigation' I just select the one named "MSTN Materials", but as described above, it reads many other sheets of the same workbook. How to avoid that?

ncbshiva Member

Re: Power BI loading unwanted worksheets on refresh



Yes i understand now, can try changing your query to below. Where that is refering to only the selected sheet.


Source = Excel.Workbook(File.Contents("Sample File Parameter16"), null, true),
#"D.I. - Competency Data Sheet_Sheet" = Source{[Item="Sheet1",Kind="Sheet"]}[Data],



Yarroo Frequent Visitor
Frequent Visitor

Re: Power BI loading unwanted worksheets on refresh

It doesn't help, still loading all the different worksheets and on top of that it returns an error "Expression.Error: The key didn't match any rows in the table."

Community Support Team
Community Support Team

Re: Power BI loading unwanted worksheets on refresh

Hi @Yarroo,


It seems that you only want to refresh the specific worksheet in Power BI Desktop.


You could click the MSTN MATERIALS then click Refresh Preview like below.




Best Regards,


Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yarroo Frequent Visitor
Frequent Visitor

Re: Power BI loading unwanted worksheets on refresh

This is not the case. As I tried to explain before, I'm not trying to exclude a single query from refresh (or refresh just single query).

I have a query like below:


2018-11-26 15_30_05-Power Query Editor.png





















So as you can see it's a single query with multiple transformation steps. It reads data from Sharepoint folder, currently there is a single file in that folder. This excel file contains multiple worksheets, among which I want to read data from only specific one ('MSTN Materials"). Full list is:


2018-11-26 15_47_05-Clipboard.png

















To get to the worksheet, I use "Invoke custom function", which has following steps of transformations for Sample file:


2018-11-26 15_33_48-Advanced Editor.png












and then the Sample file is transformed as follows:


2018-11-26 15_35_13-Advanced Editor.png

















As you can see, second step there "navigates" to a single sheet, which is "MSTN Materials", but once refreshing, Power BI goes through each and every sheet in that file instead through only this one. So my question is how to force Power BI to read rows only from "MSTN Materials" instead.

tsol01 Visitor

Re: Power BI loading unwanted worksheets on refresh



I have a similar problem. Did you find a solution for that?


Thanks you very much in advance!

Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 198 members 2,176 guests
Please welcome our newest community members: