When should I favor DAX over PowerQuery for performance

[nfuids]

Hi guys,

I have this query that does some basics steps such as loading data from a web api, select a few columns, rename them, then I create a column concatening 2 columns together, then I load a second set of data from another web api, I filter that second set of data before I do a Table.Join on them. This takes the longest time of all steps, there is less that 1000 rows in the first table and 100 for the second table..

My question is what's best done in DAX instead of PowerQuery? Like my concatenate, should I wait and do it in DAX? 

I'm not looking for a specific answer for my query but more of a general guideline as to when faver PowerQuery or Dax?

[KNP]

If you're looking for generalisations.

Data modelling in PQ. (generate your dimensions facts etc., add any required key columns/other transformations) [aiming for star schema here]

Analysis in DAX (measures, aggregated tables [maybe], avoid calculated columns in DAX)

but...

"It depends"

[Daryl-Lynch-Bzy]

Hi @nfuids - the answer is "It Depends".  Sometimes it is possible to leave the Join out of Power Query.  This is possible because the DAX/Data Model can have the relationship.  But this only works when you have a Fact Table and Dimension Table coming from the API sources.

If you need to JOIN the tables to form one fact table, you may want to consider using the Table.Buffer function on both tables before the JOIN.  This would mean that both table are loaded to memory before the join and stop Power Query thinking that it can join this in parallel.

let
   #"Source A" = Table.Buffer( #"API Call 1"),
   #"Source B" = Table.Buffer( #"API Call 2"),
   #"Join Step" = Table.Join( #"Source A", #"Source B", "Key Column")
in
   #"Join Step"

In general, I try to avoid aggregates and calculations in Power Query.  Leave this for the DAX.