Native database queries are run multiple times when refreshing

[andrewsimmans]

I am using Native Database Queries as some of the logic needed to extract data is complex and there is already well tested SQL to get the right data.

However, what I am finding is that if I build a table of data (Table1) using a Native Query and then I create a second table from the first using:

let
    Source = #"Table1", ...

then when I refresh the data from Power BI through the Gateway that the SQL under the Native Query is run multiple times (I know as it takes a while to run and I can see multiple jobs in the SQL Activity Monitor.

I had assumed that if you built one table from another that the first table would be built and then the other built from it - is there some way I can force this behaviour as having a long running SQL query running multiple times does not make sense?

[Phil_Seamark]

Hi @andrewsimmans

How much transformation are you doing in the Query Editor?

If it's not too heavy and you really need to restrict how much you call your source, you can always duplicate your table in DAX 

[andrewsimmans]

Hi Phil

I need to join tables to form what is in essence a many to many between two tables. The logic is a bit complex to explain here but this is all about handling point in time reporting and thus being able to analyse information using historical dimension information for the date being reported, I then use DAX to filter out the relevant rows based on the date selected.

So I am really wanting to know if I can force the SQL to run just once on the query it is declared as the source for and not have it run again because I use that Power Query as a source for another Power Query.

Thanks

[Anonymous]

Hi @andrewsimmans,

>>So I am really wanting to know if I can force the SQL to run just once on the query it is declared as the source for and not have it run again because I use that Power Query as a source for another Power Query.

You can create a duplicate query from original query and uncheck the "include the refresh" option of the duplicates query after the refresh.

Regards,

Xiaoxin Sheng

[andrewsimmans]

Thanks Xiaoxin

A good thought - I gave that a try - unfortunatly what that does is stop the data refreshing - I thought if I switched off refresh on Table 2 then table 2 would still pick up the refresh done in Table 1, but what happened was that Table 1 had the new data in it and Table 2 still had the old.

I then tried it the other way round switching off refresh on table 1 and switched it on for Table 2 got a surprise - table 2 had the new data in it - even though Table 1 still had the old data in it - proving that where one Table is based on another what Power Query is doing is taking the Script fromTable 1 and prefxing that onto Table 2 - rather than taking the data fro Table 1

So I guess what I want to do is impossible - I will need to think of another way of addresssing the problem.

[Anonymous]

Hi @andrewsimmans,

Did you test below steps?

1. Get data from datasource. (source table)
2. Create a duplicate query of source table and named it to backup xxx, then cancel the refresh option of current query.

3. Modify the original source data and refresh the source table.

4. Repeat steps 3,4.

After above steps, you can see the history data(backup xxx), but I found it seems only works on powr query side. If you apply the change and return to the report view, these table will show the newest data as same as source table.

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous this is not an acceptable solution as we have to modify our several reports just because of a bug of your app.

The real question is : why does a report, including only 1 query, which is associated to 1 table, runs this query multiple times ? And by multiple times I am saying that queries are sometimes run 2 times, sometimes 3 times, etc.

We want you to provide us a real technical solution to this software issue.