Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 w...
Need Help
ImkeF's avatar
ImkeF
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      ImkeF
      Community Champion
      6 years ago

      Hi Anonymous ,

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