Forum Discussion
Transformations in Power Query - Very Slow Performance
Hi Community & v-jiascu-msft,
So i managed to reduce the time of refreshing the entire model from 30mins to 4mins :smileyvery-happy:
From my intial post, there were three areas which i believed were causing the slow performance:
1. Append - multiple CSV's.
2. Merge - merge Online Fact in order to produce a Flag Field.
3. Merge - merge Online Fact with Lookup table to retrieve other fields.
What chnages did i make to devrease performance from 30mins to 4mins:
Append - multiple CSV's
Isntead of Appending each CSV as v-jiascu-msft advise i used the Folder Connector. I dont believe this caused much difference in performance, however, it does simplify and create less nedded Steps.
Merge - merge Online Fact in order to produce a Flag Field
I used a DAX formula to create a Flag to filter Transaction ID's which have at least one Product ID with a Sales value of 0.
Merge - merge Online Fact with Lookup table to retrieve other fields
I loaded the Lookup Table into the Model and created a relationship between the Online Fact and Lookup. Yes this means that i have measures seperated in tables but performance was improved dramatically. Also, needed to use the DAX Statement 'RELATED' to create measures/columns when one field was in Online Fact and another in the Lookup Table.
I have simplified the amount of work i dont to make this huge difference in refresh time: 30mins to 4mins but i believe the thing to take away from here is, dont try and do everyhting in Power Query.
Please feel free to comment on why you think the performance has increased :D
Laz
I have the same issue, I will give you some details:
1 - I need to import a Sharepoint folder with many files
2 - I need to import a file with more than 100K rows and 17 columns
3 - From 2 i get 3 tables
4 - From 1 I need to merge many tables, I don't have other option (maybe there is another way but I didn't found it) I hear that doing this with DAX will be slower.
Finally the data is restricted, so I can't share the file, I will import the query. I know that there is at least 2 possibles optimization and this is because my "entity" tables are contanstly updated, but for some reason not my fact tables and I need to grant that I can match the data with the merge, for that reason I did the same merge many times with different conditions.
The QUERY:
let
Source = #"Regional Files",
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ID", type text}, {"Source", type text}, {"Source Type", type text}, {"Testing Period", type text}, {"Issue Owner Alias", type text}, {"Owner Name", type text}, {"Issue Owner Manager Alias", type text}, {"Manager Name", type text}, {"Area", type text}, {"Region", type text}, {"SubRegion", type text}, {"Country", type text}, {"Segment", type text}, {"C&C Lead", type text}, {"Control", type text}, {"Topic", type text}, {"Issue Category", type text}, {"Issue Sub-Category", type text}, {"Type", type any}, {"Business Champ", type text}, {"Assigned To", type any}, {"Risk Priority", type text}, {"Issue Description", type text}, {"BRI Escalation", type any}, {"Failure Cause", type any}, {"FC Others#(lf)When Root Cause = Blank", type any}, {"Issue Validated", type any}, {"Sample ID", type text}, {"Customer Name", type any}, {"Patner/Vendor", type any}, {"Comments", type any}, {"Action Needed?", type text}, {"Standard Mitigation?", type any}, {"Remediation Plan", type any}, {"Preventive Plan", type any}, {"Due Date", type any}, {"Closing date", type any}, {"Closed by", type any}, {"Closing Comment", type any}, {"Evidence Link", type any}, {"Submission date", type any}, {"Issue Logged By", type any}, {"Step", type any}, {"Status", type any}}),
#"Replaced Value8" = Table.ReplaceValue(#"Changed Type",0,"",Replacer.ReplaceValue,{"Type"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value8", "Ext ID", each Text.Middle( [Source.Name], 25,3)&[ID]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom1",{{"Ext ID", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type5",{"Source.Name"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Testing Period] = "FY24 Q1")),
#"Uppercased Text" = Table.TransformColumns(#"Filtered Rows",{{"Source", Text.Upper, type text}, {"Testing Period", Text.Upper, type text}, {"Issue Owner Alias", Text.Upper, type text}, {"Area", Text.Upper, type text}, {"Region", Text.Upper, type text}, {"SubRegion", Text.Upper, type text}, {"Country", Text.Upper, type text}, {"Segment", Text.Upper, type text}, {"Control", Text.Upper, type text}, {"Topic", Text.Upper, type text}, {"Issue Category", Text.Upper, type text}, {"Issue Sub-Category", Text.Upper, type text}, {"Type", Text.Upper, type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Uppercased Text",{{"Risk Priority", Text.Proper, type text}, {"Failure Cause", Text.Proper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Capitalized Each Word",null,"",Replacer.ReplaceValue,{"Control", "Topic", "Issue Category", "Issue Sub-Category", "Type"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Customer Name", type text}, {"Patner/Vendor", type text}, {"Comments", type text}, {"Action Needed?", type text}, {"Standard Mitigation?", type text}, {"Remediation Plan", type text}, {"Preventive Plan", type text}, {"Due Date", type date}, {"Closing date", type date}, {"Closed by", type text}, {"Closing Comment", type text}, {"Evidence Link", type text}, {"Submission date", type date}}),
#"Replaced Value4" = Table.ReplaceValue(#"Changed Type1","SALES DEAL EXECUTION","SALES DEALS EXECUTION",Replacer.ReplaceText,{"Topic"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value4", {{"Due Date", null}, {"Closing date", null}, {"Submission date", null}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Errors",{{"Issue Logged By", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each if [Testing Period] = Null.Type then null else
if Number.FromText( Text.Middle([Testing Period],6,1)) = 1 then #date(1999 + Number.FromText( Text.Middle([Testing Period],2,2)), 9,30) else
if Number.FromText( Text.Middle([Testing Period],6,1)) = 2 then #date(1999 + Number.FromText( Text.Middle([Testing Period],2,2)), 12,30) else
if Number.FromText( Text.Middle([Testing Period],6,1)) = 3 then #date(2000 + Number.FromText( Text.Middle([Testing Period],2,2)), 3,30) else
#date(2000 + Number.FromText( Text.Middle([Testing Period],2,2)), 6,30)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom", type date}}),
#"Replaced Errors1" = Table.ReplaceErrorValues(#"Changed Type4", {{"Custom", null}}),
#"Uppercased Text1" = Table.TransformColumns(#"Replaced Errors1",{{"Step", Text.Upper, type text}, {"Status", Text.Upper, type text}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Uppercased Text1",{{"Step", type text}, {"Status", type text}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type3", {"Topic", "Control", "Issue Category", "Issue Sub-Category", "Type"}, #"Cat&Topic", {"Topic", "Control", "Category", "Subcategory", "Type"}, "Cat&Topic", JoinKind.LeftOuter),
#"Expanded Cat&Topic1" = Table.ExpandTableColumn(#"Merged Queries1", "Cat&Topic", {"Index"}, {"Cat&Topic.Index"}),
#"Merged Queries" = Table.FuzzyNestedJoin(#"Expanded Cat&Topic1", {"Control", "Topic", "Issue Category", "Issue Sub-Category"}, #"Cat&Topic", {"Control", "Topic", "Category", "Subcategory"}, "Cat&Topic", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1]),
#"Expanded Cat&Topic" = Table.ExpandTableColumn(#"Merged Queries", "Cat&Topic", {"Index", "Control_1"}, {"Cat&Topic.Index.1", "Cat&Topic.Control_1"}),
#"Merged Queries11" = Table.NestedJoin(#"Expanded Cat&Topic", {"Control", "Issue Sub-Category", "Type"}, #"Cat&Topic", {"Control", "Subcategory", "Type"}, "Cat&Topic", JoinKind.LeftOuter),
#"Expanded Cat&Topic2" = Table.ExpandTableColumn(#"Merged Queries11", "Cat&Topic", {"Index"}, {"Cat&Topic.Index.2"}),
#"Added Custom" = Table.AddColumn(#"Expanded Cat&Topic2", "Cat&Topic Index", each if [#"Cat&Topic.Index"] = null then
if [#"Cat&Topic.Index.2"] <> null then [#"Cat&Topic.Index.2"] else
if [#"Cat&Topic.Control_1"] = "Only4" then [#"Cat&Topic.Index.1"]
else [#"Cat&Topic.Index"]
else [#"Cat&Topic.Index"]),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom",{{"Cat&Topic Index", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type6",{{"Ext ID", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Cat&Topic.Index", "Cat&Topic.Index.1", "Cat&Topic.Control_1"}),
#"Merged Queries2" = Table.NestedJoin(#"Removed Columns1", {"Area", "Region", "SubRegion", "Country"}, #"Area Structure", {"Area", "Region", "SubRegion", "Country"}, "Area Structure", JoinKind.LeftOuter),
#"Expanded Area Structure" = Table.ExpandTableColumn(#"Merged Queries2", "Area Structure", {"Index"}, {"Area Structure.Index"}),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded Area Structure",null,-1,Replacer.ReplaceValue,{"Area Structure.Index"}),
#"Removed Columns2" = Table.RemoveColumns(#"Replaced Value1",{"Area", "Region", "SubRegion", "Country"}),
#"Merged Queries3" = Table.NestedJoin(#"Removed Columns2", {"Issue Owner Alias"}, Employees, {"Email"}, "Employees", JoinKind.LeftOuter),
#"Expanded Employees" = Table.ExpandTableColumn(#"Merged Queries3", "Employees", {"Personnel Number"}, {"Employees.Personnel Number"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Employees",{{"Employees.Personnel Number", "Employee ID"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns3",{"Issue Owner Alias", "Owner Name", "Issue Owner Manager Alias", "Manager Name"}),
#"Merged Queries4" = Table.NestedJoin(#"Removed Columns3", {"Segment"}, Segments, {"Segment"}, "Segments", JoinKind.LeftOuter),
#"Expanded Segments" = Table.ExpandTableColumn(#"Merged Queries4", "Segments", {"Index"}, {"Segments.Index"}),
#"Removed Columns4" = Table.RemoveColumns(#"Expanded Segments",{"Segment", "Risk Priority"}),
#"Merged Queries5" = Table.NestedJoin(#"Removed Columns4", {"Failure Cause"}, #"Failure Causes", {"Failure Cause"}, "Failure Causes", JoinKind.LeftOuter),
#"Expanded Failure Causes" = Table.ExpandTableColumn(#"Merged Queries5", "Failure Causes", {"Index"}, {"Failure Causes.Index"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded Failure Causes",{{"Failure Causes.Index", "Failure Index"}}),
#"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"Failure Cause"}),
#"Merged Queries6" = Table.NestedJoin(#"Removed Columns5", {"Step"}, Steps, {"Steps"}, "Steps", JoinKind.LeftOuter),
#"Expanded Steps" = Table.ExpandTableColumn(#"Merged Queries6", "Steps", {"Index"}, {"Steps.Index"}),
#"Renamed Columns5" = Table.RenameColumns(#"Expanded Steps",{{"Steps.Index", "Steps Index"}}),
#"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"Step"}),
#"Merged Queries7" = Table.NestedJoin(#"Removed Columns6", {"Status"}, Status, {"Status"}, "Status.1", JoinKind.LeftOuter),
#"Expanded Status.1" = Table.ExpandTableColumn(#"Merged Queries7", "Status.1", {"Index"}, {"Status.1.Index"}),
#"Renamed Columns6" = Table.RenameColumns(#"Expanded Status.1",{{"Status.1.Index", "Status Index"}, {"Segments.Index", "Segments Index"}, {"Area Structure.Index", "Area Index"}}),
#"Merged Queries8" = Table.NestedJoin(#"Renamed Columns6", {"Source Type"}, #"Source Origin", {"Origin"}, "Source Origin", JoinKind.LeftOuter),
#"Expanded Source Origin" = Table.ExpandTableColumn(#"Merged Queries8", "Source Origin", {"Index"}, {"Source Origin.Index"}),
#"Renamed Columns7" = Table.RenameColumns(#"Expanded Source Origin",{{"Source Origin.Index", "Origin Index"}}),
#"Removed Columns7" = Table.RemoveColumns(#"Renamed Columns7",{"Source Type"}),
#"Replaced Value6" = Table.ReplaceValue(#"Removed Columns7","SCA ","SCA",Replacer.ReplaceText,{"Source"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","REPORTED BY MSC PO DESK ","REPORTED BY MSC PO DESK",Replacer.ReplaceText,{"Source"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value7", "Source", "Source - Copy"),
#"Merged Queries10" = Table.NestedJoin(#"Duplicated Column", {"Source"}, #"Source Eq", {"Old Sources"}, "SegmentEq", JoinKind.LeftOuter),
#"Expanded SegmentEq" = Table.ExpandTableColumn(#"Merged Queries10", "SegmentEq", {"New Sources"}, {"SegmentEq.New Sources"}),
#"Removed Columns8" = Table.RemoveColumns(#"Expanded SegmentEq",{"Source"}),
#"Renamed Columns10" = Table.RenameColumns(#"Removed Columns8",{{"SegmentEq.New Sources", "Source"}}),
#"Merged Queries9" = Table.NestedJoin(#"Renamed Columns10", {"Source"}, Sources, {"Source"}, "Sources", JoinKind.LeftOuter),
#"Expanded Sources" = Table.ExpandTableColumn(#"Merged Queries9", "Sources", {"Index"}, {"Sources.Index"}),
#"Renamed Columns8" = Table.RenameColumns(#"Expanded Sources",{{"Sources.Index", "Source Index"}}),
#"Renamed Columns9" = Table.RenameColumns(#"Renamed Columns8",{{"Custom", "Reference Date"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns9",null,-1,Replacer.ReplaceValue,{"Employee ID"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,-1,Replacer.ReplaceValue,{"Segments Index"}),
#"Removed Columns9" = Table.RemoveColumns(#"Replaced Value3",{"Source"}),
#"Replaced Value5" = Table.ReplaceValue(#"Removed Columns9",null,2,Replacer.ReplaceValue,{"Status Index"})
in
#"Replaced Value5"
END QUERY
This query is connected with this:
let
Source = SharePoint.Contents("https://microsoftapc.sharepoint.com/teams/PWCI/", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
MRC = #"Shared Documents"{[Name="MRC"]}[Content],
#"Regional Files" = MRC{[Name="Regional Files"]}[Content],
#"Filtered Rows" = Table.SelectRows(#"Regional Files", each ([Extension] = ".xlsx"))
in
#"Filtered Rows"