Transformations in Power Query - Very Slow Performance

[Anonymous]

Hi Power BI Community,

I have a solution that is working very slow and wanted to get everyone input in order to get some understanding on what is causing a hit on performance. So what do I mean by ‘slow’?

Firstly, In Power Query, after I apply a Append or Merge, Power Query takes forever to complete previewing the result (completion is indicated in bottom right).

Secondly, One I click ‘Close and Apply’ to load the data to the Model, it takes over 30mins.

Also, if anyone has any tips on monitoring performance levels in Power Query to understand what’s causing the slow performance, would be very much appreciated.

I have 5 CSV Files:

CSV UK = 1.7M Rows (132 MB)

CSV AU = 205K Rows (15.8 MB)

CSV CZ = 5.5K Rows (445 KB)

CSV EU = 108K Rows (8.02 MB)

CSV ROW = 228K Rows (16.8 MB)

All these file are appended in Power Query starting from the ‘CSV UK’ as the below shows:

#"Appended Query" = Table.Combine({#"Added Custom", AU, CZ, EU, ROW})

The files above are allowing us to see each Transactions and Items that belong in each Transaction with measures such as Sales Value. This Query has been named Online Fact.

Q: Does the Append already add much strain on the solution? The reason I ask is, once I click on the Append Step in the ‘Applied Steps’ panel, Power Query takes quite some time to COMPLETE previewing the data.

Following this, I need to add a Flag Field to act as a filter for Transactions that have at least 1 item with a Sales value of 0.

To do this, I Duplicated the ‘Online Fact’ and renamed to ’Zero Sales’ in Power Query and:

1. Added a Filter on Sales Value = 0
2. Removed Duplicated
3. Added a Custom Column which repeats ‘Y’ across all rows and named it ‘AdjZeroFlag’

Doing this allowed by to have a single Data Set which shows only Transactions which have at least one item with a 0 Sales Value. I then done a LEFT OUTER MERGE:

#"Merged Queries" = Table.NestedJoin(#"Sorted Rows",{"TransactionID"},#"Zero Sales",{" TransactionID "},"Zero Sales",JoinKind.LeftOuter),

#"Expanded Zero Sales" = Table.ExpandTableColumn(#"Merged Queries", " Zero Sales", {"AdjZeroFlag"}, {"AdjZeroFlag"}),

Q: Does the LEFT OUTER MERGE add more performance issues to the overall solution?

The final step is to get some measures from a different source which are missing from the initial Text Files. This source is a SQL DB and I called it in Power Query ‘Lookup’.

The Query Returns 2.7M records and will be used to return the Sales Cost which is missing from the initial Power BI Query ‘Online Fact’. The way I approached this was by using once more, a LEFT OUTER MERGE:

#"Merged Queries1" = Table.NestedJoin(#"Added Custom6",{"OrderID", "ProductID"},#"LOOKUP",{"OntransRef", "StyleBarcode"},"LOOKUP",JoinKind.LeftOuter),

Once I add this Merge everything starts to work very slow. In fact, Power Query is taking ages to only to preview the data on screen. Once it finally does and I click ‘Close and Apply’ to load the model. It takes over 30mins for the data to load.

Important: The Only Table I am loading to the model is the Power BI Query ‘Online Fact’, as I unticked the ‘Enable Load’ option for the other as all they were needed for was to do the LEFT OUTER MERGE and APPEND.

What steps will I take to try increase overall performance:

1. I will try append all the CSV files outside from Power BI, that way less work is needed from Power Query.
2. Instead of Duplicating the entire ‘Online Fact’ and using it to do a LEFT OUTER MERGE, what I will try do is find an alternative method for deriving a Flag that indicates whether the Transactions has at least 1 item with a 0 Value.
   1. I will explore this in DAX using a Calculated Column, as I am hoping I can find a way to apply the logic at the Transaction ID level, even though the lowest level in the ‘Online Fact’ is Transaction Item.
   2. I will remove the LEFT OUTER MERGE for returning back the Sales Cost and instead, load the ‘Lookup’ and simply create a relationship with the ‘Online Fact’ and ‘Lookup’.
      1. Even though this is frustrating, as I would like to have simply added the measure in the Online Fact.

Thank you PBI Community

Laz

[JMAvila]

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"

[Anonymous · Accepted solution]

Hi Community & @v-jiascu-msft,

So i managed to reduce the time of refreshing the entire model from 30mins to 4mins 

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 😄

Laz

[ArshadHashmi]

HI,

I am also facing the same issue, only initaily load tackes a time, but no huge data no complex queries. but the initial load tacks 10 to 15 minutes. there is any resolution. kindly help.

[v-jiascu-msft]

Thanks for sharing. @Anonymous. I'm glad you made it.

If the dataset is small, everything is fine. If the dataset is large, we have to keep everything simple. For example, less join or merge which could double the data and slow down the performance. 

Best Regards,
Dale

[v-jiascu-msft]

Hi Laz,

I would suggest you only do "Get data" in the Power Query. Other steps are reasonable to do with DAX. 

1. Since you only use the appended table, you can try the connector Folder that will combine the CSV files at one time. So you don't need to import the individual files.

2. Why did you extract 0 sales in the Power Query? Usually, this should be done with DAX. Either a measure or a calculated column. 

3. If you use DAX instead in item 2 above, the Left Outer Join wouldn't necessary.

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft,

Thank you for the reply.

In regards to your notes, i am assuming that when you say i should only do 'Get Data' in Power Query, this means i should not apply any of the transformations i am doing (mainly the Merges) as this can be done in DAX?

Also coming back to you on your points:

1. Since you only use the appended table, you can try the connector Folder that will combine the CSV files at one time. So you don't need to import the individual files.

I will certainly do this; I believed that appending individually each CSV, would have the same outcome in performance as it would when using the connector Folder.

1. Why did you extract 0 sales in the Power Query? Usually, this should be done with DAX. Either a measure or a calculated column.

In all honestly, the reason I chose to Duplicate the ‘Online Fact’, filter all records which have a 0 Sales value, remove all fields apart from the Transaction ID, Remove all Duplicates and MERGE with the original ‘Online Fact’ was due to knowing how to do this in DAX.

I did not know the method to do this in DAX using a measure or column as the dataset in question goes down to Transaction Item level. The filed I am looking to derive is a Flag indicating whether a Transaction ID has at least one Item with a 0 Value. Therefore, this measure or column should be based on Transaction ID level, whilst using the data from the Transactions Item level.

1. If you use DAX instead in item 2 above, the Left Outer Join wouldn't necessary.

I don’t get this point, I can’t see DAX helping me when I want to pull some data from a different source into the ‘Online Fact’ Query. For instance, as mentioned the ‘Online Fact’ is coming from some CSV files, but they are missing the Sales Cost field. To get the Sales Cost I need to write a query to the SQL DB Source. Once I get this data into Power BI, I need to pull this data into the ‘Online Fact’. The way I currently do this, is through a LEFT OUTER MERGE. However, performance is an issue, hence I was thinking of simply joining the two tables using the Relationships. The only frustrating aspect of this, is that i am loading another table into the Model, only to get a singl field which is the Sales Cost.

I will be applying these changes and get back to you and the community with my findings.

Thanks again for the reply.

Laz

[v-jiascu-msft]

Hi Laz,

Can you share a little sample? A dummy one is enough.

Regarding the last part, if you don't have a query of "0 sales", you don't need to apply "left outer join" unless you'd like to join other queries. 

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft

Looking to resolve this and will get back with an update 🙂

Thanks,

Laz