Forum Discussion
Insert current date then make it static and not refresh on later days
Hi all,
I have a table of products, one product per row. I would like a custom column in which, for each row, once a product status changes from "Fail" to "Pass" (in a status column), the custom column is populated with the current date of when the query was refreshed, and the status changed. The query is being refreshed every day.
Then, I want this automatically inputted date to remain fixed, so that on susequent days when the data is refreshed, the populated dates in the custom column do not change, and only any newly inputted dates reference the current date.
How can this be done?
Thanks!
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
2 Replies
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
