Last Refresh Semantic Model (Power Query)

createOrReplace

	/// Last refresh of semantic model - not the underlying job and pipelines
	table 'Last Refresh'
		column TimeStamp
			dataType: dateTime
			formatString: d mmm yyyy hh.nn
			displayFolder: Dates
			summarizeBy: none
			sourceColumn: TimeStamp

			annotation SummarizationSetBy = Automatic

			annotation PBI_FormatHint = {"isDateTimeCustom":true}

		column 'Date Last Refresh'
			dataType: dateTime
			formatString: Long Date
			displayFolder: Dates
			summarizeBy: none
			sourceColumn: Date Last Refresh

			annotation SummarizationSetBy = Automatic

			annotation UnderlyingDateTimeDataType = Date

		column 'Time Last Refresh'
			dataType: dateTime
			formatString: Long Time
			displayFolder: Dates
			summarizeBy: none
			sourceColumn: Time Last Refresh

			annotation SummarizationSetBy = Automatic

			annotation UnderlyingDateTimeDataType = Time

		partition 'Last Refresh' = m
			mode: import
			queryGroup: 'Meta Data & Organize'
			source =
					/* M-Query - Data Last Refreshed */
					
					let
					    SummerTime = Date.StartOfWeek( #date(Date.Year( DateTime.LocalNow() ), 3, 31) , Day.Sunday ),
					    WinterTime = Date.StartOfWeek( #date(Date.Year( DateTime.LocalNow() ), 10, 31) , Day.Sunday ),
					    CurrentDateTime = DateTimeZone.RemoveZone( DateTimeZone.UtcNow() ),
					    TimeShiftFromUTC = if CurrentDateTime < SummerTime & #time( 2, 0, 0) or CurrentDateTime > WinterTime & #time( 2, 0, 0) then 1 else 2,
					    TimeStamp = DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), TimeShiftFromUTC, 0),
					    #"Converted to Table" = #table(1, {{TimeStamp}}),
					    #"Renamed Column1 to TimeStamp" = Table.RenameColumns(#"Converted to Table",{{"Column1", "TimeStamp"}}),
					    #"Changed Type TimeStamp" = Table.TransformColumnTypes(#"Renamed Column1 to TimeStamp",{{"TimeStamp", type datetimezone}}),
					    #"Inserted Col Date" = Table.AddColumn(#"Changed Type TimeStamp", "Date", each DateTime.Date([TimeStamp]), type date),
					    #"Inserted Col Time" = Table.AddColumn(#"Inserted Col Date", "Time", each DateTime.Time([TimeStamp]), type time),
					    #"Renamed Col Date and Time" = Table.RenameColumns(#"Inserted Col Time",{{"Date", "Date Last Refresh"}, {"Time", "Time Last Refresh"}})
					in
					    #"Renamed Col Date and Time"