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
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:
- 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.
- 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.
- 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
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