Forum Discussion

AlvinB's avatar
AlvinB
Frequent Visitor
1 year ago
Solved

Too slow performance in querying in each

Hi all, I'm facing to the slow performance in below query in DataFlow: I am trying to calculate the days from the document lodged date until today by excluding weekend & public holidays. I am not ...
Need Help
  • AlvinB's avatar
    AlvinB
    1 year ago

    I am still trying to reduce the performance time and one of my efforts was to separate the calculation logic of weekend and public holidays from queries and it reduced the time to run so far.

    I am not sure why but after I separated the calculation logic to a function and calling that from queries, performance has been enhanced.

    Outstanding = Table.AddColumn(_decided, "DaysUntilToday",
        each (
            if [Lodged_date] = null then 0
            else (
                fx_calBusinessDays(DateTime.Date([Lodged_date]), _toDate)
            )
        )
    ),

    When I tested with the original code with fully implemented 25 queries, it runs about / more than 2 hours but now it runs about an hour. Thanks.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, 

Thanks for the solution shafiz_p  offered, and i want to offer some more informaiton for user to refer to.

hello AlvinB , based on the code you have offered, you have about 32k rowa about the holiday, it is too large, maybe you can consider to reduce the amount of data in the calendar table, such as extracting only the data of the last one or two years, and extract the date table data of the most recent date commemoration according to the date you need now, so as to reduce the size of the calendar table data and improve efficiency.

And you can use diagnostics to  diagnose which step is running the longest so that the step can be optimized more accurately

You can refer to the following link about it.

Query diagnostics - Power Query | Microsoft Learn

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.