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.

shafiz_p's avatar
shafiz_p
Super User
1 year ago

Hi AlvinB 
Instead of calculating the number of weekends and public holidays for each row individually, you can precompute these counts for a range of dates and store them in a separate table. Then, you can join this precomputed table with your main table.

 

Power Query’s list functions can be more efficient than table operations. You can try to use List.Dates and List.Count to count the number of weekends and public holidays.

Try this:

 

let
    // Get data from a specific table
    Source = Databricks.Catalogs(HostName, HttpPath, [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),
    #"Navigation 1" = Source{[Name = Catalog, Kind = "Database"]}[Data],
    #"Navigation 2" = #"Navigation 1"{[Name = "catalog", Kind = "Schema"]}[Data],
    _table = #"Navigation 2"{[Name = "table", Kind = "Table"]}[Data],

    // Extract all weekend and public holidays from the Calendar table
    _weekendOrHoliday = Table.SelectColumns(Table.SelectRows(Calendar, each [IsWeekend] = 1 or [IsPublicHoliday] = 1), "Primary_key_date"),
    _weekendOrHolidayList = List.Buffer(Table.Column(_weekendOrHoliday, "Primary_key_date")),

    // Get today to calculate days until today
    _toDate = DateTime.Date(DateTime.LocalNow()),

    _result = Table.AddColumn(_table, "DaysUntilToday",
        each (
            if [Date_Lodged] = null then 0
            else (
                let
                    _fromDate = DateTime.Date([Date_Lodged]),
                    _days = Duration.Days(_toDate - _fromDate),
                    _dateRange = List.Dates(_fromDate, _days, #duration(1, 0, 0, 0)),
                    _minus = List.Count(List.Intersect({_dateRange, _weekendOrHolidayList}))
                in
                    _days - _minus
            )
        )
    )
in
    _result

 



 Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

 

 

Best Regards,
Shahariar Hafiz

  • AlvinB's avatar
    AlvinB
    Frequent Visitor
    1 year ago

    This morning, I was running the dataflow and this dataflow contains 25 queries and one of them contains the script I've posted. I was refreshing the dataflow 2 times. One with the original script and the other with the script you've suggested. Unfortunately, my dataflow runs faster with the original script rather than LIST function. With table select function, it runs about 25 mins otherwise about 40 mins with LIST function. The problem is I need to implement this code to several queries and this is why I need to enhnace my code. Thanks.