Skip to content

Instantly share code, notes, and snippets.

View SchreiberLars's full-sized avatar

Lars Schreiber SchreiberLars

22 followers · 5 following
View GitHub Profile
@SchreiberLars
SchreiberLars / fnGeschaeftsHalbjahr.pq
Created June 28, 2018 15:02
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),
@SchreiberLars
SchreiberLars / fnGeschaeftsHalbjahrUndJahr.pq
Created June 28, 2018 15:00
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),
@SchreiberLars
SchreiberLars / fnGeschaeftsQuartalUndJahr.pq
Last active June 28, 2018 14:58
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),
@SchreiberLars
SchreiberLars / fnGeschaeftsQuartal.pq
Last active June 28, 2018 14:55
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),
@SchreiberLars
SchreiberLars / fnGeschaeftsMonatUndJahr.pq
Last active June 28, 2018 14:50
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),
@SchreiberLars
SchreiberLars / fnGeschaeftsmonat.pq
Last active June 28, 2018 14:46
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),
@SchreiberLars
SchreiberLars / fnGetISOCalendarWeekName.pq
Created May 30, 2018 15:38
(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
@SchreiberLars
SchreiberLars / fnGetSequentialMonthNumber.pq
Last active May 30, 2018 13:35
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),
@SchreiberLars
SchreiberLars / fnGetSequentialHalfYearNumber.pq
Last active May 30, 2018 13:31
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),
@SchreiberLars
SchreiberLars / fnGetSequentialQuarterNumber.pq
Last active May 30, 2018 13:07
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),