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,
i am trying to calculate network days from two columns of dates.
StartDate EndDate
02/02/2021 | 02/02/2021 |
02/02/2021 | 03/02/2021 |
02/02/2021 | 08/02/2021 |
10/03/2021 | 19/03/2021 |
null | 20/03/2021 |
02/02/2021 | null |
08/02/2021 | 02/02/2021 |
conditional logic:
1. take into account weekends and PublicHolidays1
2. If either of the values are null to return a null
3. if the start date is greater than the created date to return a null
here is the function i am using: kindly provided by @Jimmy801
(StartDate as date, EndDate as date) as number =>
let
IntStartDate = if StartDate = null then null else StartDate ,
IntEndDate = if EndDate = null then null else EndDate ,
ListDates = List.Dates(IntStartDate, Number.From(IntEndDate - IntStartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, PublicHolidays1),
CountDays = List.Count(RemoveHolidays)
in
if IntStartDate > IntEndDate then null else CountDays
i am getting this error and am lost as to how to fix it.
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
thanks
Amrita
Solved! Go to Solution.
HI Jimmy, i used the TRY and removed the error so all good. thanks!
I suspect your PublicHolidays1 query is in table form, and a list is expected in the function. Is that the case? If so, you can replace it with PublicHolidays1[ColumnName], which will pull that single column (replace with actual name of it) from the PublicHolidays1 table as a list.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
hi pat yes publicholidays1 i have changed the code and its working great - except - i am now gettign this error where the value should return as null
Expression.Error: We cannot convert the value null to type Date.
Details:
Value=
Type=[Type]
Hi Amrita,
I am also getting the same error. Did you manage to resolve it?
HI Jimmy, i used the TRY and removed the error so all good. thanks!
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.