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"
