Forum Discussion

maibacherstr's avatar
maibacherstr
Icon for Helper III rankHelper III
5 years ago
Solved

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

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...
Need Help
Tips and Tricks
Tutorial Requests
  • edhans's avatar
    edhans
    5 years ago

    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.

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
maibacherstr
Icon for Helper III rankHelper III
5 years ago
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.