— — —

M Query

Literatur:

https://docs.microsoft.com/en-us/powerquery-m/

Last refresh:

In M Query eine leere Abfrage einfügen und dann im Advanced Editor einfügen:

let

Source = #table(type table[Date RS last Refreshed=datetime], {{DateTime.LocalNow()}})

in

Source

Die Query z.B. „Date RS“ benennen.

Dies erzeugt dann eine Tabelle mit einem Eintrag, der den letzten Zeitpunkt des refreshs anzeigt:

Den Wert dann in ein card oder eine Tabelle ziehen.

let

Source = DateTimeZone.FixedLocalNow

//Source = DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),-1)

in

Source

ergänzen.

Date Table in M Query

// Calendar Funktion umgestellt auf M Query
let
    // get current date, week, month, quarter and year 
    #"This Date" = Date.From( DateTime.LocalNow() ),
    #"This Week" = Date.WeekOfYear( #"This Date"  ),
    #"This Month" = Date.Month( #"This Date"  ),
    #"This Quarter" = Date.QuarterOfYear( #"This Date"  ),
    #"This Year" = Date.Year( #"This Date"  ),
    
    // get calendar start and dates 
    #"Start Date" = #date( #"This Year" -20, 1, 1 ),
    #"End Date" = #date( #"This Year" + 1, 12, 31 ),

    // create date table
    #"List all Dates" = let 
        #"Duration from Start to End" = Duration.Days( #"End Date" - #"Start Date" ) + 1,
        #"List Dates" = List.Dates( #"Start Date", #"Duration from Start to End", #duration( 1, 0, 0, 0 ) ),
        #"Table Dates" = #table( type table [ #"Date" = date ], List.Transform( #"List Dates", each { _ } ) )
    in
        #"Table Dates",

    // add all columns
    #"Added rDate" = Table.AddColumn( #"List all Dates", "rDate", each Number.From( [Date] - #"This Date" ), Int64.Type ),
    #"Added Week" = Table.AddColumn( #"Added rDate", "Week", each " W" & Text.PadStart( Number.ToText( Date.WeekOfYear( [Date] ) ), 2, "0" ), type text ),
    #"Added Week_No" = Table.AddColumn( #"Added Week", "Week_No", each Number.From( Date.WeekOfYear( [Date] ) ), Int64.Type ),
    #"Added rWeek" = Table.AddColumn( #"Added Week_No", "rWeek", each Date.WeekOfYear( [Date] ) - #"This Week", Int64.Type ),
    #"Added Month" = Table.AddColumn( #"Added rWeek", "Month", each Text.Start( Date.MonthName( [Date] ), 3 ), type text ),
    #"Added Month_No" = Table.AddColumn(#"Added Month", "Month_No", each Date.Month([Date])),
    #"Added rMonth" = Table.AddColumn( #"Added Month_No", "rMonth", each Date.Month( [Date] ) - #"This Month", Int64.Type ),
    #"Added Quarter" = Table.AddColumn( #"Added rMonth", "Quarter", each "Q" & Number.ToText( Date.QuarterOfYear([Date] ) ), type text ),
    #"Added rQuarter" = Table.AddColumn(#"Added Quarter", "rQuarter", each Date.QuarterOfYear( [Date]) - #"This Quarter", Int64.Type ),
    #"Added Year" = Table.AddColumn(#"Added rQuarter", "Year", each Date.Year([Date]),Int64.Type ),
    #"Added rYear" = Table.AddColumn( #"Added Year", "rYear", each Date.Year( [Date] ) - #"This Year", Int64.Type ),
    #"Added Year Week" = Table.AddColumn(#"Added rYear", "Year Week", each Number.ToText([Year]) & " " & [Week]),
    #"Added rYearWeek" = Table.AddColumn(#"Added Year Week", "rYearWeek", each ( [rYear] * 53 ) + [rWeek], Int64.Type ),
    #"Added Year Month" = Table.AddColumn(#"Added rYearWeek", "Year Month", each Number.ToText([Year]) & " " & [Month]),
    #"Added rYearMonth" = Table.AddColumn( #"Added Year Month", "rYearMonth", each ( [rYear] * 12 ) + [rMonth], Int64.Type ),
    #"Added Year Quarter" = Table.AddColumn(#"Added rYearMonth", "Year Quarter", each Number.ToText([Year]) & " " & [Quarter]),
    #"Added YearQuarters" = Table.AddColumn(#"Added Year Quarter", "rYearQuarter", each ( [rYear] * 4 ) + [rQuarter], Int64.Type),


    #"prev_3_month" = Table.AddColumn(#"Added YearQuarters", "f_prev_3_month", each Date.IsInPreviousNMonths([Date], 3)),
    #"prev_Y_month" = Table.AddColumn(#"prev_3_month", "f_prev_Y_month", each Date.IsInPreviousNMonths([Date], 11) and Date.IsInCurrentYear([Date])),
    #"this_year" = Table.AddColumn(#"prev_Y_month", "f_this_Year", each Date.IsInCurrentYear([Date])),
    #"prev_5_years" = Table.AddColumn(#"this_year", "f_prev_5_years", each Date.IsInPreviousNYears([Date], 5) or
        Date.IsInCurrentYear([Date])),
    #"Changed Type" = Table.TransformColumnTypes(prev_5_years,{{"f_prev_5_years", type logical}, {"f_this_Year", type logical}, {"f_prev_3_month", type logical}, {"f_prev_Y_month", type logical}})
in
    #"Changed Type"