This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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()), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 = |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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" |
NewerOlder