Lars Schreiber SchreiberLars

## 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"

## 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 =

## 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

## 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()),

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

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

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

## 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

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

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