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), |
OlderNewer