Query help in power query

[Anonymous]

Hello All!

I have two tables called Orders and hist_Orders. These tables contain recent and historical order data. I have set the hist_Orders table to not refresh. In the Orders table I then append the hist_Orders table so that I can provide the user a single table with new and older order information.  During refeshes, I am experincing a long refresh time. I had the DB admin run a trace on the refresh and he sees  a select from the Orders table unioned with a select from the hist_Orders table [what I expected], however each time the refresh is done, a new select from hist_Orders is executing [not what I expected]. I expected it to load the hist_Orders table once and then just refresh the Orders table. How do I prevent the hist_Orders table from continually being refreshed?

Cheers!

Peter

[samdthompson · Accepted solution]

Hello, I dont believe the method your using now will work because the hist_Orders table, although set to not refresh is being added to, thus the dont refresh is over ridden but the update. What you will need to do is use the incremental refresh:

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Based on my test, if a single query (TableA) is not included in report refresh, it will not be refreshed. If it is appended to another query (TableB) that is included in refresh, the TableB query will be refreshed completely, including refreshing the appended TableA section in it. When you load both queries to the model and update one value in the data source of the TableA, you will find that the TableA in the model is not updated while the TableB is updated with the latest value from the TableA after refreshing. 

If you don't want to query data from the hist_Orders table in the data source, instead of appending them in Power Query Editor, you can try using UNION function in DAX to append two tables. This union process happens after querying data process is completed and data has been loaded into the model. However, this is done at the model layer so you cannot do data cleaning/transformation like what you can using Power Query after appending queries. 

Another option is to use Power BI Dataflows. Create a dataflow to keep the static data of hist_Orders table. Connect to this dataflow to get data, then append it to the Orders table. When you refresh data, it queries data from the dataflow rather than from its original data source. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[samdthompson · Accepted solution]

Hello, I dont believe the method your using now will work because the hist_Orders table, although set to not refresh is being added to, thus the dont refresh is over ridden but the update. What you will need to do is use the incremental refresh:

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

[Anonymous]

Hello,

  I am not sure I understand. Will you ellaborate?

Peter