Refresh takes a long time

[simp]

Hello,

I am connecting to excel file in my LAN,

But it takes a long time to get the data,

The excel file contains 11 sheet, 12 named range, and 7 table format (and each table only less than 100 rows)

Some cells in table contain data validation connected to named range list,

I think what make it takes so long is because Power BI try to read all sheet, named range and table,

Anyway my connection is only for some table and excel named range,

How to tell PBI to read only specific area so i can get the data in much shorter time?

This is an example of the query for data source:

Source = Excel.Workbook(File.Contents("\\xx.xx.xx.xx\FolderName\FileName.xlsx"), null, true),
xyz = Source{[Name="SheetName"]}[Data],

[shakeelsyed]

Hi Team,

similar kind of issues I am facing while updating/refreshing tables from ODBC connections, it’s almost taking more than 2hours to update a simple query and there is another system with same configuration which my team mate  is using at the moment which will take less than 2min to run the same query.

I have tried uninstalling/installing Power bi Desktop, ODBC connection strings, Data gateways but none of that was helpful.

Any clue on how can I improve the performance.

Below are the configuration details and I am using PostgreSQL.

 

[shakeelsyed]

Hi Team,

similar kind of issues I am facing while updating/refreshing tables from ODBC connections, it’s almost taking more than 2hours to update a simple query and there is another system with same configuration which my team mate  is using at the moment which will take less than 2min to run the same query.

I have tried uninstalling/installing Power bi Desktop, ODBC connection strings, Data gateways but none of that was helpful.

Any clue on how can I improve the performance.

Below are the configuration details and I am using PostgreSQL.

 

[shakeelsyed]

Hi Team,

similar kind of issues I am facing while updating/refreshing tables from ODBC connections, it’s almost taking more than 2hours to update a simple query and there is another system with same configuration which my team mate  is using at the moment which will take less than 2min to run the same query.

I have tried uninstalling/installing Power bi Desktop, ODBC connection strings, Data gateways but none of that was helpful.

Any clue on how can I improve the performance.

Below are the configuration details and I am using PostgreSQL.

[v-yulgu-msft]

Hi @simp,

Actually, when connecting to an Excel file from Power BI desktop, if the excel file contains multiple worksheets and multiple table formats, we can select those necessary tables or sheet that we need. Please see below image:

Regards,
Yuliana Gu

[simp]

Hi @v-yulgu-msft,

What i mean is after we click Refresh to the query (Home > Refresh),

In the popup box we can see it is evaluating, waiting, etc

It loops to all of them (Sheets, tables, named ranges) which is unnecessary because i dont use all of them

[v-yulgu-msft]

Hi @simp,

So, you only want to update some certain tables when clicking on refresh button, instead of updating all data records imported from Excel, right?

If so, you can disable query refesh for some unnecessary queries so that they won't be refreshed together with others.

Query editor -> right click the unnecessary query -> select properties -> uncheck the 'Include in report refresh' option.

Best regards,
Yuliana Gu

[simp]

Hello @v-yulgu-msft,

If you see the refresh box, you will see:

Evaluating...

Waiting for Other Queries...

Create connection to...

Waiting for xxx

9 rows from xxx

etc

For: Waiting for and x rows from... 

The Power BI read all of Sheets, Tables and Named Ranges include that we dont use in queries...

[v-yulgu-msft]

Hi @simp,

Below is the screenshot when I refresh data models, the table 'Sheet2' that I have disabled its refresh is not included.

Regards,
Yuliana Gu

[simp]

Hi @v-yulgu-msft,

Sorry but this is not about my queries that i dont want to run,

I am asking why Power BI looping through all sheets, tables and named ranges in my excel file (data source) that i dont want to transform (See in Refresh box)