Why is my query so slow?

[Walt1010]

I run a query that merges 2 other queries into it, each of which performs very similar functions. When I do anything (for example close and load) the processing is very slow consider the size of the files. The "Load" screen shows, with the name of one or more of the queries, each with one of the file names, the one for the bank holidays file, displaying, with an extremely slow counter showing an increase in the size of that file being processed:

That gives me the clue that they way I'm handling my use of the bank holidays file may be inefficient. My files contain the shift dates and times of 50 staff, each shift of which has to be checked whether it occurred fully or partially within a Bank Holiday. So each shift entry needs to be checked once if it is only during one day, and twice if it ocurrs overnight. the m-code I'm using is:

if List.PositionOf(#"Bank_Holidays"[Initial Date],DateTime.Date([Start])) >= 0 then "Yes" else "No"

Is that an inefficient method?

[johnbasha33]

Hi @Walt1010 

Yes, your current method is not the most efficient, especially when dealing with a large number of rows and a relatively small bank holiday table. Let’s break down what’s happening and why it may be slow:

Efficient Alternative: Use a Merge (Join)

Here’s a better approach:

1. Create a new column in your shift table for each date you want to check (e.g., ShiftDate1, ShiftDate2).

2. Convert your Bank Holidays to a table (you already have one).

3. Use Merge Queries in Power Query to join the shift table with the Bank Holidays table on each of the dates (ShiftDate1 to Initial Date, ShiftDate2 if needed).

After the merge, add a column to flag whether a match occurred ("Yes" or "No").

Example:

Assume:

• Your shift table has [Start] and [End] as DateTime.

• You want to check both start and end dates.

Step-by-step:

Add columns:
= Table.AddColumn(YourShiftTable, "StartDate", each DateTime.Date([Start]))
= Table.AddColumn(_, "EndDate", each DateTime.Date([End]))

• Merge with Bank_Holidays table twice:

  • Merge StartDate with Bank_Holidays[Initial Date]

  • Merge EndDate with Bank_Holidays[Initial Date]

Add custom column:
= if [MergedStart] <> null or [MergedEnd] <> null then "Yes" else "No"Remove the merge columns after use (optional).

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[v-aatheeque]

Hi @Walt1010 ,

If the information provided by @mh2587 @johnbasha33  is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[v-aatheeque · Accepted solution]

Hi @Walt1010 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[mh2587]

•  List.PositionOf is an expensive operation:

  • It scans the entire list every time it's called.

  • If your main table has thousands of shifts, it runs thousands of separate searches through the list.

• No caching:

  • Power Query doesn't cache intermediate list lookups by default. So even if multiple shifts start on the same date, each will trigger a full scan.

• Inefficient merge/joins:

  • If you’re merging two queries that also use this check, you may be duplicating expensive logic.
    
    Try the following appraoch:
    Instead of scanning the list using List.PositionOf , convert your Bank_Holidays table to a distinct Date table and join it to your shifts table using a merge. Merging is much more efficient because it's implemented more like a hash join.

[Walt1010]

Thanks! So do I need to transform the table of holiday dates to a Date Table, specifically? Why is that?