Forum Discussion
Native database queries are run multiple times when refreshing
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
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
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
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.
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