cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smatnz Frequent Visitor
Frequent Visitor

Power Query slow refresh after adding 'Invoked Custom Column'

Hi all,

 

New to Power BI and Power Query here.

 

I've created my first data model from an Excel file and a separate Excel file for dimensions (a few tables for calendar and lookups).

I needed a net working hours to measure how long each request takes to process.

 

After adding the custom function that data refresh has become very slow. I could see Excel reading through the data line by line.

 

I've unchecked a few boxes in Settings to help speed up the load and read countless threads about buffer and folding but they dont seem to be applicable for me or I simply don't know how to use it.

 

Can anyone please take a look at my query and let me know how I can fix it?

 

Many thanks!

---------------------------------------------------------

let
    fCRMExtract = let
    Source = Excel.Workbook(File.Contents("\\SAKLFILE4\GlobalShare\T&O - Operations\ACC\ACC Operational Dashboard Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Case Number.", type text}, {"Customer Segment", type text}, {"Customer Name: Customer Name", type text}, {"Submit to", type text}, {"Type of Request", type text}, {"Approved/Declined by: Full Name", type text}, {"Status", type text}, {"Case Owner: Org Unit L5 Name", type text}, {"Approved within Lender DCA", type text}, {"Initiator: Full Name", type text}, {"Account Manager: Full Name", type text}, {"Account Manager: Org Unit L4 Name", type text}, {"Account Manager: Org Unit L5 Name", type text}, {"Has Been Deferred?", Int64.Type}, {"Deferral Reasons", type text}, {"Date/Time Opened", type datetime}, {"Date Assigned", type datetime}, {"Date/Time Closed", type datetime}, {"SLA Status", type text}, {"Closed", Int64.Type}, {"Priority Reason", type any}, {"Priority", type text}, {"Centralised Team Processor: Full Name", type text}, {"Approved/Declined by: Org Unit L4 Name", type text}, {"Approved/Declined by: Org Unit L5 Name", type text}, {"Short Form Credit Submission Utilised?", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date/Time Opened", "Date/Time Opened - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date/Time Opened - Copy", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date/Time Opened - Copy", "Date Opened"}, {"Date Assigned", "Date/ Time Assigned"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Date/ Time Assigned", "Date/ Time Assigned - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Date/ Time Assigned - Copy", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Date/ Time Assigned - Copy", "Date Assigned"}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns1", "Date/Time Closed", "Date/Time Closed - Copy"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Duplicated Column2",{{"Date/Time Closed - Copy", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Date/Time Closed - Copy", "Date Closed"}})
   
in
    #"Renamed Columns2",
    WHStart=Number.From(pWHstart),
    WHEnd=Number.From(pWHend),
    #"Merged Queries" = Table.NestedJoin(fCRMExtract,{"Type of Request"},dSLAThresholds,{"Types of Request"},"dSLAThresholds",JoinKind.LeftOuter),
    #"Expanded dSLAThresholds" = Table.ExpandTableColumn(#"Merged Queries", "dSLAThresholds", {"SLA Thresholds (Hrs)", "SLA Group"}, {"SLA Thresholds (Hrs)", "SLA Group"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded dSLAThresholds",{"Submit to"},dTargets,{"Decisioned By"},"dTargets",JoinKind.LeftOuter),
    #"Expanded dTargets" = Table.ExpandTableColumn(#"Merged Queries1", "dTargets", {"Target Deferral", "Target Cycle Time", "Target SLA", "Target Points"}, {"Target Deferral", "Target Cycle Time", "Target SLA", "Target Points"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded dTargets",{{"Target Deferral", Percentage.Type}, {"Target SLA", Percentage.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fxNetWorkingHours", each fxNetWorkingHours(WHStart, WHEnd, [#"Date/ Time Assigned"], [#"Date/Time Closed"], Holidays)),
    #"Multiplied Column" = Table.TransformColumns(#"Invoked Custom Function", {{"fxNetWorkingHours", each _ * 24, type number}}),
    #"Changed Type1" = Table.Buffer(Table.TransformColumnTypes(#"Multiplied Column",{{"SLA Thresholds (Hrs)", Int64.Type}}))

in
    #"Changed Type1"

1 ACCEPTED SOLUTION

Accepted Solutions
smatnz Frequent Visitor
Frequent Visitor

Re: Power Query slow refresh after adding 'Invoked Custom Column'

Thanks @v-yuta-msft I finally figured out how to use Table.Buffer and List.Buffer and it works like a charm.

 

cheers

 

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Power Query slow refresh after adding 'Invoked Custom Column'

Hi smatnz,

 

To improve the performance of power query, please refer to the documentation: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance and this thread: https://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables....

 

Regards,

Jimmy Tao

smatnz Frequent Visitor
Frequent Visitor

Re: Power Query slow refresh after adding 'Invoked Custom Column'

Thanks @v-yuta-msft I finally figured out how to use Table.Buffer and List.Buffer and it works like a charm.

 

cheers

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)