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"
