Skip to content

Instantly share code, notes, and snippets.

Lars Schreiber SchreiberLars

Block or report user

Report or block SchreiberLars

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
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"
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 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 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 fnGetSequentialQuarterNumber.pq
let
fn = (TableWithDateColumn as table, DateValue as date) as number =>
let
GetYearQuarter = Table.AddColumn(TableWithDateColumn , "JahrQuartal", each Date.Year([Datum])*100+Date.QuarterOfYear([Datum])),
GetListOfYearQuarter = GetYearQuarter[JahrQuartal],
ConvertToTable = Table.FromList(GetListOfYearQuarter, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "JahrQuartal"}}),
ChangeType = Table.TransformColumnTypes(RenameColumn,{{"JahrQuartal", Int64.Type}}),
RemoveDuplicates = Table.Distinct(ChangeType),
AddIndex = Table.AddIndexColumn(RemoveDuplicates, "SequentialQuarterNumber", 1, 1),
View fnGetSequentialHalfYearNumber.pq
let
fn = (TableWithDateColumn as table, DateValue as date) as number =>
let
GetYearHalfYear = Table.AddColumn(TableWithDateColumn , "JahrHalbJahr", each Date.Year([Datum])*100 + (if Date.Month([Datum]) < 7 then 1 else 2) ),
GetListOfYearQuarter = GetYearHalfYear[JahrHalbJahr],
ConvertToTable = Table.FromList(GetListOfYearQuarter, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "JahrHalbJahr"}}),
ChangeType = Table.TransformColumnTypes(RenameColumn,{{"JahrHalbJahr", Int64.Type}}),
RemoveDuplicates = Table.Distinct(ChangeType),
AddIndex = Table.AddIndexColumn(RemoveDuplicates, "SequentialHalfYearNumber", 1, 1),
View fnGetSequentialMonthNumber.pq
let
fn = (TableWithDateColumn as table, DateValue as date) =>
let
GetYearMonth = Table.AddColumn(TableWithDateColumn, "JahrMonat", each Date.Year([Datum])*100+ Date.Month([Datum])),
GetListOfYearMonth = GetYearMonth[JahrMonat],
ConvertToTable = Table.FromList(GetListOfYearMonth , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "JahrMonat"}}),
ChangeType = Table.TransformColumnTypes(RenameColumn,{{"JahrMonat", Int64.Type}}),
RemoveDuplicates = Table.Distinct(ChangeType),
AddIndex = Table.AddIndexColumn(RemoveDuplicates, "SequentialMonthNumber", 1, 1),
View fnGetISOCalendarWeekName.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 = "ISO KW "& Text.From(Number.IntegerDivide(Part1 + Part2, 7))
in
ISOWeekNumber
View fnGeschaeftsmonat.pq
let
fn = (MyDate as date, StartMonat as text) =>
let
Startmonatsnummer =
List.PositionOf(
{"januar", "februar","märz","april","mai","juni", "juli", "august", "september", "oktober", "november", "dezember"},
Text.Lower(StartMonat)
) + 1,
Offset = Startmonatsnummer -1,
AtuellerKalendermonat = Date.Month(MyDate),
View fnGeschaeftsMonatUndJahr.pq
let
fn = (MyDate as date, StartMonat as text) =>
let
Startmonatsnummer =
List.PositionOf(
{"januar", "februar","märz","april","mai","juni", "juli", "august", "september", "oktober", "november", "dezember"},
Text.Lower(StartMonat)
) + 1,
Offset = Startmonatsnummer -1,
AtuellerKalendermonat = Date.Month(MyDate),
You can’t perform that action at this time.