Power BI loading unwanted worksheets on refresh

[Anonymous]

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?

 

[ncbshiva]

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

[Anonymous]

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]

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

[Anonymous]

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

[v-piga-msft]

Hi @Anonymous,

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,

Cherry

[Anonymous]

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:

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:

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

and then the Sample file is transformed as follows:

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]

Hey,

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

Thanks you very much in advance!