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 fnUTC2Local.pq
(DateTimeUTC as datetime) as datetime =>
let
/*
================================================================================
Wesentliche Bestandteile der Funktion basieren auf der Arbeit von 'Frank Tonsen'
================================================================================
*/
Year = Date.Year(DateTimeUTC),
//Ermittlung der Grenze der Winterzeit --> letzter Sonntags im März, 00:59:59 Uhr UTC
View fnRelativerSpaltenvergleich.pq
let
/*
Beginn der Funktionsdefinition, mit Funktionsnamen, Funktionsparametern und Typ des Funktionsrückgabewertes
*/
fnInsertDiffColumn = (ColumnName as text, MyTable as table) as table =>
let
/*
Parameter für das Testen der Funktion, während der Entwicklungsphase
*/
/*
View fnGeschaeftsHalbjahr.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 fnGeschaeftsHalbjahrUndJahr.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 fnGeschaeftsQuartalUndJahr.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 fnGeschaeftsQuartal.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),
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 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 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),
You can’t perform that action at this time.