Power Query or Azure Data Factory ADF V2 When to use one over the other

[smjzahid]

I would like to know when it becomes more sensible to use ADFV2 (Mapping Dataflow) for data transformation. At my workplace we use Power Query and Power BI dataflow as well as Power Platform dataflow to transform our data and apply business logic. Both (Power BI dataflow and Power Platform Dataflow) as well as the Query Editor uses M in the background to write the transformation on data and applies each step. I have played around with ADV2 (Mapping Dataflow) and it presents you with a nice GUI to apply transformation to your data and in the background uses JSON to record all transformation. My question is when both technologies (Power BI Dataflow) and ADF V2 does same thing, How do we decide when to use one over the other and why would someone go and invest in ADF V2 (I mean pay for each individual pipeline run like Compute and Storage) when they get similar data transformation capabilities built in within Power BI desktop and POwer BI Dataflow (considering Pro License is in place) which costs much cheaper then ADFV2 Any help will be much appreciated on this, as to when to use ADFV2 over Power BI dataflow

[Dhrubojit]

I have the same question, when we use adf Or azure data pipeline vs powerquery

[DeepEureka]

I guess that the difference is that ADFV2 let you save the results of the transformations. Personally I would store them in parquet format o feather file format to improve space or performance.

In PowerQuery the result is stored in-memory in the model each time you refresh. So if you have a large database maybe it makes sense to store old transactions already transformed, transform on the spot only new transactions and append bith of them in to the PowerBI model.

Probably you save tons of time loading huge models this way. Once transformed (1 run of the ADFV2 pipeline) you simply read from it as a Source of your model without so much data wrangling...

[Anonymous]

I came accross the same question. Any thoughts would be appreciated!
Many content refers to implementing Azure Data Factory coming from SSIS. But what about coming from Power Query (Power BI Desktop / Dataflows)? 

[v-eachen-msft]

Hi @smjzahid ,

According to your requirements, you could refer to this blog for more details:

https://www.microsoft.com/en-us/us-partner-blog/2017/12/19/why-azure-data-factory-version-2/

[smjzahid]

This does not answer my question, as to which technology to choose, It simple is an intorduction to ADF V2 and nothing else mentioned there.

Please read my concern again to understand it better