Performance power query vs dax

[stigcorneer]

I have a couple of tables with about 100k - 800k rows each.

I need to do the following: (excel equivalent)
1. Joining tables (vlookup)
2. Aggregated table joins (sumifs)
3. Conditional calculations (if.. Then x+y else y*z)
4. Straight up calculations (x+y*z)

What parts should I do in Power query and what should I use dax for best performance?

 

[Matt Allington]

These are jobs for DAX, although you can do almost anything in power query. If you did it in P, you would be building a bespoke solution. If you do it in DAX, you would be building a data model. Read about it here What is a Power BI Data Model? - Excelerator BI

 

[stigcorneer]

Yes I know that I can use either tool. But what combination will give the best performance with large datasets?

My idea is to use Power Query to load the data and do all joins and aggregations. (Step 1 and 2)
Then I load the data into the Datamodel and use DAX for calculations.

 

[Matt Allington]

You should not build aggregations and joins in PQ, you should build a data model and do it in DAX. That is the right approach and the most performant.

 

[stigcorneer]

Òk, thanks