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