Forum Discussion
Insert current date then make it static and not refresh on later days
Hi bjpowell93 ,
Power Query is a mashup tool, not a data warehouse, therefore the refresh process looks like this:
Wipe all data > Get current data from source > Perform transformations
As such, you can't 'fix' data like this within Power Query, it would either have to come from the source, or be partitioned into history using incremental refresh.
Options might be:
1) Create a 'today's date' column in your query, then use incremental refresh to only refresh a very narrow partition each day, so the dates in the historical partition are retained:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
2) Have a Slowly Changing Dimension (SCD) table built on the source, such as an SQL stored procedure run daily on the source, with the results written to a table each time.
Pete
Hi bjpowell93 ,
Power Query is a mashup tool, not a data warehouse, therefore the refresh process looks like this:
Wipe all data > Get current data from source > Perform transformations
As such, you can't 'fix' data like this within Power Query, it would either have to come from the source, or be partitioned into history using incremental refresh.
Options might be:
1) Create a 'today's date' column in your query, then use incremental refresh to only refresh a very narrow partition each day, so the dates in the historical partition are retained:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
2) Have a Slowly Changing Dimension (SCD) table built on the source, such as an SQL stored procedure run daily on the source, with the results written to a table each time.
Pete
Hi BA_Pete,
Many thanks for clarifying the situation, which is unfortunate. My issue is that my data source is an Excel workbook containing c.450,000 rows for all the products and I don't want to have to add in a static date against each of these rows every time a product status changes to "Pass". Additionally, I don't think my laptop or Excel could tolerate that quantity of rows to be usable.
I'll have to think some more on how this can be done in an easy way that's as automated as possible.
Thanks,
Ben