How to Automatically Name the Query with a Date Stamp from the Date of Refresh

[maibacherstr]

Hello,

I would like to find a way to add a date stamp to my query names. I'm in the midst of learning parameters and am trying to put the pieces together. Here's what I'm working with:

I have a data source that receives updates periodically, but the filename which is "Project Schedule", never changes. I would like to set my scheduled refresh to a weekly cycle, and each time the data refreshes, two things happen: a timestamp applies to the query name (such as: "Project Schedule" source name becomes "Project Schedule 08 30 2021"), and, the new "Project Schedule 08 30 2021" is added into my list of queries that grows by 1 new query each week.

The key is to achieve this using scheduled refresh/automation.

Thank you very much in advance,

Dan

[edhans · Accepted solution]

You cannot. Query names are part of the metadata and neither DAX nor Power Query can touch those, and Power Query cannot generate new queries dynamically like this.

You would need to add this information as part of a field within the query.

[maibacherstr]

I was afraid of the first part, but thank you for clarifying this. If I'm able to take the date of refresh, and add that into a column, then I don't mind if I need to append each week, rather than through the first method I described.

That said, I would want to take the date of refresh, and add it into a column such that it will append THIS WEEK's refresh, to the sum total of all previous weeks' data.

Is this possible?

Thanks again.

[edhans]

No, you cannot do that either. The issue is when you refresh, everything refreshes, so you get 1 date for all refreshed data. It will not keep track of what you did last week vs this week. The right way to do that is to add the "data date" to the source data. If it were Excel files for example, you could use the "last saved date" from the file, But even that might not be correct if someone opened and saved, without changing data, a 3 week old file. It would have today's date.

There is an interesting hack for this in this article by tricking Power BI into thinking it is an incremental refresh, but I would not recommend it for a production environment. If you updated your data and republished your file, all of your historical dates would get trashed unless you used ALM Toolkit to preserve and change the dates back.
Chris Webb's BI Blog: Incremental Refresh Chris Webb's BI Blog (crossjoin.co.uk)

But if the source data had the data date, then it would just always work.

Remember, Power BI when it refreshes (other than legit Incremental Refresh) it essentially deletes the entire data model and reloads it from scratch. And even with true incremental refresh, it isn't retaining refresh dates like you are envisioning. It is just partitioning the data so it can save time on large data model refreshes.

[maibacherstr]

Thank you very much for the discussion into this topic!

More broadly, the big weakness that I'm working with right now is that there is no way to store historical data in the report (or anywhere else so far as this journey has taken me...), hence I'm severely hampered when it comes to presenting change over time; such as, for instance, a Project Schedule that evolves day by day throughout the project execution, based on Actual Start/Actual Finish performance data.

I've designed a rather great "delta" graph to show this, but it requires historical data that I have no way of compiling presently, except by excruciating manual process.

When you mentioned about query names being stored as meta data, I knew my last suggestion was doomed to fail, which is that if M would allow for a query to run once, ie. as it is created, then disable the refresh (a manual click but needs automation in this case...) then perhaps this would provide an opportunity to build out the list of queries that I mentioned would grow +1 with each weekly refresh. It's my last hail mary, but I really doubt it.

Thanks for mentioning Chris Webb's blog; previously, I've combined 3 blog posts (2 of his and 1 from another site) into a 6-page step by step for loading locally stored, high res, filterable images to a report. It's quite amazing especially behind the DoD firewall or whenever web-sourced images aren't preferred. Let me know if this step by step is something that would help you...

Last, fortunately I'm on a Microsoft Premier Services plan and will bring the topic of logging historical data to THEM next time. More likely though, I think the real task would belong to my schedule management team, but they would have to change their way of doing business by stamping each updated schedule with the date of publication in the filename (in keeping with your discussion earlier).

Thanks again.