Last Refresh table in Databricks or in power query?

[victoryamaykin]

I have this extra table to get the last refresh time. Is it more efficient to have this saved in Power Query or build a dedicated table in our Azure Databricks gold layer? Thanks

let

date = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5,0)),

time = DateTime.Time(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5,0)),

//negative five (-5) is based off of CST adjust this on date and time to your timezone.


firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),

SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),


isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0))

         or

 (date > SecondSundayOfMarch and date < firstSundayOfNovember) 

 or 

 (date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (Number.From(isSummerTime))*1 - 1, 

//negative one (-1) may need to be adjusted depending on your timezone. 

ltime = 

            DateTime.From(date) 

            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  

            + #duration(0, timeZone, 0, 0),

    #"Converted to Table" = #table(1, {{ltime}}),

    #"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),

    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Column1", "Time"}, {"Column1 - Copy", "Date"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}, {"Date", type date}})

in

    #"Changed Type"

[v-tejrama]

Hi  @victoryamaykin ,

Thank you  @tayloramy   for the response provided! 

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[tayloramy · Accepted solution]

Hi @victoryamaykin, 

 
There are actually two useful timestamps: (1) when your data was last loaded into your warehouse/lake (“data freshness”), and (2) when your Power BI model was last refreshed (“report freshness”). Where you generate the timestamp determines which one you’re showing.

 

• Report freshness (recommended in Power BI): Create a one-row table in Power Query using DateTimeZone.FixedUtcNow() so it’s stamped at refresh time and stays consistent across all queries during that refresh. Convert to local time in the report if needed. See the M/Power Query date-time functions here: Power Query date functions.
• Data freshness (recommended in Databricks): Write a tiny “meta” table in your gold layer that your orchestration updates at the end of the pipeline (e.g., SELECT current_timestamp() AS data_last_loaded). Then show both values in the report: “Data last loaded” (gold) and “Report last refreshed” (Power Query).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[victoryamaykin]

This is perfect. Thank you