cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 "$":

 

MSTN MATERIALS$

MSTN MATERIALS ALL$

MSTN MATERIALS ALL$_FILTERDATABASE

SHEET6$

SHEET2$

SHEET4$

SHEET4$_FILTERDATABASE

SHEET1$

 

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

7 REPLIES 7
ncbshiva Member
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:

 

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

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
Member

Re: Power BI loading unwanted worksheets on refresh

Hi,

 

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

 

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

 

Regards

Highlighted
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.

 

refresh.png

 

Best Regards,

Cherry

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
Visitor

Re: Power BI loading unwanted worksheets on refresh

Hey,

 

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

 

Thanks you very much in advance!

Helpful resources

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 119 members 1,570 guests
Please welcome our newest community members: