Skip to content

Instantly share code, notes, and snippets.

Avatar

Lars Schreiber SchreiberLars

View GitHub Profile
View fnGetCalenderTable.pq
let
fn=(StartJahr, EndJahr, optional Culture, optional StartFiskaljahr)=>
let
StartFiskJahr = if StartFiskaljahr = null then "Jan" else StartFiskaljahr,
GetStartDay = #date(StartJahr,1,1),
GetEndDay = #date(EndJahr,12,31),
GetCultureDefaultGermany = if Culture = null then "de-de" else Culture,
DayCount = Duration.Days(Duration.From(GetEndDay - GetStartDay)) + 1,
GetListOfDates = List.Dates(GetStartDay,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(GetListOfDates, Splitter.SplitByNothing()),
View fnGetIsoWeekNumber.pq
(MyDate as date) =>
//Source --> https://blogs.office.com/en-us/2009/06/30/week-numbers-in-excel/
let
//MyDate = #date(2016,1,3),
Part1 = Number.From(MyDate) - Number.From(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) + 1) + 4)),1,3)),
Part2 = Date.DayOfWeek(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) +1) + 4)),1,3), Day.Sunday)+1 + 5,
ISOWeekNumber = Number.IntegerDivide(Part1 + Part2, 7)
in
ISOWeekNumber
View GetTableOfDateAndDateTime.pq
let
fn = (Start as any, End as any, Step as number, Unit as text, SplitDateAndTime as text) as table =>
let
TypeStart = GetTypeAsText(Start),
TypeEnde = GetTypeAsText(End),
GetTypeAsText = (Value as any) as text =>
let
nonNullableType = Type.NonNullable(Value.Type(Value)),
FindType =
View fnGetTypeAsText.pq
(Value as any) as text=>
let
nonNullableType = Type.NonNullable(Value.Type(Value)),
FindType =
if Type.Is(nonNullableType, type binary) then "binary"
else if Type.Is(nonNullableType, type date) then "date"
else if Type.Is(nonNullableType, type datetime) then "datetime"