Referencing external tables column in function

[Anonymous]

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!

[ImkeF]

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/  

[Anonymous]

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

[ImkeF]

Hi @Anonymous ,

are you able to share a sample workbook with some mockup data please?

[amitchandak]

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/

[Anonymous]

Hello @amitchandak - Would you have additional insight into this topic, or would you advise me to raise this topic somewhere else?

[amitchandak]

@ImkeF , @Ashish_Mathur  can you help on this

[Anonymous]

Thanks.
I should have said this before I think, but I'm working in the PowerQuery editor here...
I'll update the question in a sec.

I guess this advice won't work there?