Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
This url gives me the french bank holidays : https://calendrier.api.gouv.fr/jours-feries/metropole.json
I have a table with a datetime column called CreationDateTime. Can you please tell me how I can add a personnalized column that would calculate the duration between now and CreationDateTime, not counting the saturdays nor the sundays nor the bank holidays there could be in between ?
Solved! Go to Solution.
I have made up this solution which seems to work so far :
...
fnDuration = (startDate as datetime, endDate as datetime) =>
let
DurationDays = Duration.Days(endDate - startDate),
ListDates = List.Dates(Date.From(startDate), DurationDays + 1, #duration(1, 0, 0, 0)),
ListWeekDays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
ListHolidays = List.Transform(ListWeekDays, each Date.ToText(_, "yyyy-MM-dd")),
Url = "https://calendrier.api.gouv.fr/jours-feries/metropole/" & Text.From(Date.Year(startDate)) & ".json",
Holidays = Json.Document(Web.Contents(Url)),
#"Converti en table" = Record.ToTable(Holidays),
#"Colonnes supprimées" = Table.RemoveColumns(#"Converti en table",{"Value"}),
HolidaysTrimmed = Table.ToList(#"Colonnes supprimées"),
ListInterSect = List.Intersect({ListHolidays, HolidaysTrimmed}),
Result = endDate - startDate - #duration(List.Count(ListInterSect) + DurationDays + 1 - List.Count(ListWeekDays), 0, 0, 0)
in Result,
AddedCustom = Table.AddColumn(#"Type modifié", "DiffDate", each fnDuration([sys_created_on], DateTime.FromText(DateTime.ToText(DateTime.LocalNow(), [Format="dd-MMM-yyyy HH:mm:ss", Culture="en-US"])))),
#"Type modifié3" = Table.TransformColumnTypes(AddedCustom,{{"DiffDate", type duration}}),
...
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello,
Thank you but I was referring to do that in Power Query M language, do you know then please ?
let
Source = Json.Document(Web.Contents("https://calendrier.api.gouv.fr/jours-feries/metropole.json")),
Ferie = List.Transform(Record.FieldNames(Source), Date.From),
Date_Start = #date(2017,2,7),
Date_End = #date(2020,5,19),
#"Excluded Ferie" = List.Difference(List.Dates(Date_Start, Duration.Days(Date_End - Date_Start) + 1, #duration(1,0,0,0)), Ferie),
#"Excluded Weekend" = List.Count(List.Select(#"Excluded Ferie", each Date.DayOfWeek(_, Day.Monday)<5))
in
#"Excluded Weekend"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello, it's kind of what I want, but the need is to have the duration in a new column of a table, being the time elapsed between the value in column CreationDateTime (which is a DateTime and not a Date) and now (including the time part). Do you know how to do that ?
I have made up this solution which seems to work so far :
...
fnDuration = (startDate as datetime, endDate as datetime) =>
let
DurationDays = Duration.Days(endDate - startDate),
ListDates = List.Dates(Date.From(startDate), DurationDays + 1, #duration(1, 0, 0, 0)),
ListWeekDays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
ListHolidays = List.Transform(ListWeekDays, each Date.ToText(_, "yyyy-MM-dd")),
Url = "https://calendrier.api.gouv.fr/jours-feries/metropole/" & Text.From(Date.Year(startDate)) & ".json",
Holidays = Json.Document(Web.Contents(Url)),
#"Converti en table" = Record.ToTable(Holidays),
#"Colonnes supprimées" = Table.RemoveColumns(#"Converti en table",{"Value"}),
HolidaysTrimmed = Table.ToList(#"Colonnes supprimées"),
ListInterSect = List.Intersect({ListHolidays, HolidaysTrimmed}),
Result = endDate - startDate - #duration(List.Count(ListInterSect) + DurationDays + 1 - List.Count(ListWeekDays), 0, 0, 0)
in Result,
AddedCustom = Table.AddColumn(#"Type modifié", "DiffDate", each fnDuration([sys_created_on], DateTime.FromText(DateTime.ToText(DateTime.LocalNow(), [Format="dd-MMM-yyyy HH:mm:ss", Culture="en-US"])))),
#"Type modifié3" = Table.TransformColumnTypes(AddedCustom,{{"DiffDate", type duration}}),
...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.