Forum Discussion
Referencing external tables column in function
Hi all,
Hope you can help me with this.
My basic question is, how can I reference an external table from within a function from within the PowerQuery editor.
Here's my setup:
I have a table with tasks, and one with Holidays.
For my monitoring, I am using a function that will deduct the holidays from the actual task duration.
Table "TASK"
| Task ID | Start | End |
| aaa001 | 2020-01-05 | 2020-01-06 |
| aaa002 | 2020-01-12 | 2020-02-01 |
| aaa003 | 2020-04-01 | 2020-04-15 |
| aaa004 | 2020-04-09 | 2020-04-20 |
Table "HOLIDAY"
| Holiday name | Date |
| New years Eve | 2020-01-01 |
| Good Friday | 2020-04-10 |
| Easter Sunday | 2020-04-12 |
| Easter Monday | 2020-04-13 |
| Labour Day | 2020-05-01 |
Now, to get the info from the "Holidays" table into my function, so far I have:
- Joined the Holiday table with my Tasks table (all [HOLIDAYS.Date] to each of [Task ID])
- Ran my function, referencing the newly created [HOLIDAYS.Date] column in the [TASKS] table
// working days are from Mon=0 to Fri=4 and not in holiday list
fnIsWorkingDay = (dt as date) as logical =>
Date.DayOfWeek(dt, Day.Monday) < 5 and not List.MatchesAny(HolidayList, each _ = dt)
- Removed the column
As the [TASKS] table is huge (with >60k entries), the join is taking quite some time, and is very inefficient.
Is there a way, to directly reference the [HOLIDAYS.Date] column as a list, instead of joining and using what's called "HolidayList" in the function above?
That would probably take out most of the processing time for me...
Edit: marked that I am working in the PowerQuery editor, not in PowerBI directly. If the question needs to be moved, please do so. Sorry for the inconvenience!
7 Replies
Create a date table and merge your holidays in that. And refer to working day formula in the given file between two dates and use that
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
In this file check how date has been to Order dim. Same way you can move holiday to date table and create 1,0 for working and non working days
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Hello amitchandak - Would you have additional insight into this topic, or would you advise me to raise this topic somewhere else?
ImkeF , Ashish_Mathur can you help on this
Hi Anonymous ,
there are a couple of things here that don't align:
1) How can you join a date with a number? ("Joined the Holiday table with my Tasks table (all [HOLIDAYS.Date] to each of [Task ID])")
2) What is that "HolidayList" that you're refering in the function? There is no mention/description of it so far:
// working days are from Mon=0 to Fri=4 and not in holiday list
fnIsWorkingDay = (dt as date) as logical =>
Date.DayOfWeek(dt, Day.Monday) < 5 and not List.MatchesAny(HolidayList, each _ = dt)In case you're looking for a NETWORKDAYS function that handles holidays as well, please check out my blogpost here: https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/
Hello ImkeF,
Sorry I was not clear earlier.What I am doing is joining all entries from the table "HOLIDAYS" to each entry from the table "TASKS". Result of this process is a list that is located in a separate column, called "HolidayList", that I am then checking against Start- and end date.
The code for this step in Power Query is:
=Table.AddColumn(#"LastStep", "Holidays", each HOLIDAYS[Date])Afterwards, I am passing this table to the function that I described earlier calculating the elapsed time as parameter "HolidayList".
Hi Anonymous ,
are you able to share a sample workbook with some mockup data please?
