Reference Table update before a refresh....

[Spudduk]

Question for the experts out there...

I have a table from Excel which is imported into BI via a query.  At the moment the user triggers an update via the manual refresh option.

The data in the table is updated with the latest set of updates so updates the fields rather than creates new.

I was trying to use the REFERENCE function to make a snapshot of the table before the refresh so I could compare the 2 object fields the now with the before. 

The example is Timeloss where if the value was 20 mins before and now it is 30 mins then they have lost a further 10 mins.

Is there a way to automate the whole process so the REFERENCE runs before the refresh or is this something I will have to leave on the back burner for now.

Andy

[v-lionel-msft]

Hi @Spudduk ,

How about doing like this so that one of the tables is not included in the refresh, so that when you manually refresh in the data model layer, the original table will not be refreshed.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Spudduk]

Apologies... Using the Power Query Editor to Create the reference table, Select Table and right click... Reference option,.

[selimovd]

Hey @Spudduk ,

in which language do you use the "REFERENCE function"? I honestly don't know what that is.

I personally would try to create snapshots in your data source, for example in SQL Server. Like this you have persistant values and you can analyze afterwards everything you want to.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic