Azure Data Factory Enables Data Wrangling at Scale with Power Query

Published 01-21-2021 02:52 PM 9,590 Views
Microsoft

The Azure Data Factory team is excited to announce a new update to the ADF data wrangling feature, currently in public preview. Wrangling in ADF empowers users to build code-free data prep and wrangling at cloud scale using the familiar Power Query data-first interface, natively embedded into ADF. Power Query provides a visual interface for data preparation and is used across many products and services. With Power Query embedded in ADF, you can use the PQ editor to explore and profile data as well as turn your M queries into scaled-out data prep pipeline activities. Data Flows in ADF and Synapse Analytics will now focus on Mapping Data Flows with a logic-first design paradigm, while the Power Query interface will enable the data-first wrangling scenario.

 

 

With Power Query in ADF, you now have a powerful tool to use in your ADF ETL projects for data profiling, data prep, and data wrangling. You have immediate feedback from introspection of your Lake and database data with the Power Query M language available for your data exploration. You can then take your resulting mash-up and save it as a first-class ADF object and orchestrate a data pipeline with that same M Power Query executing on Spark.

 

power-query-activity.png

 

When you have completed your data exploration, save your work as a Power Query object and then add it as a Power Query activity on the ADF pipeline canvas. With your Power Query activity inside of a pipeline, ADF will execute your M query on Spark so that your activity will automatically scale with your data by leveraging the ADF data flow infrastructure.

 

power-query-pipeline.png

 

In the example above, I added my Power Query activity to my pipeline for cleaning addresses from my ingested Lake data folders with Power Query, then handing the results off to a Data Flow via ADLS Gen2, where I perform data deduplication and then use the ADF pipeline to send emails when the process completes.

 

Because you are in the context of an ADF pipeline, you can define destination sinks for your Power Query mash-up so that you can persist the results of your transformations to data store like ADLS Gen2 storage or Synapse Analytics SQL Pools. Leverage the power of ADF to define source and destination mappings, database table settings, file and folder options, and other important data pipeline properties that data engineers need when building scalable data pipelines in ADF.

 

Click here to learn more about Azure Data Factory and the power of data wrangling at cloud scale with the new updated Power Query public preview feature in ADF.

 

7 Comments
Occasional Visitor

Nice!!

What is it using under the hood?

 

New Contributor

Power Query the World! :beaming_face_with_smiling_eyes:

Microsoft

@timrobertsusa Spark computing is used under hood to execute PowerQuery  expressions in a managed cluster. That gives you performance and scalability. 

New Contributor
I tried it's an awesome feature
New Contributor

Hi,
One question - Power QUery is only available in ADF at the moment, right? I cannot find it in Synapse Pipelines.. 

Microsoft

@wstrasser Correct. This feature has not yet been ported to Synapse. 

New Contributor

Thank you @Mark Kromer !

%3CLINGO-SUB%20id%3D%22lingo-sub-2084863%22%20slang%3D%22en-US%22%3EAzure%20Data%20Factory%20Enables%20Data%20Wrangling%20at%20Scale%20with%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2084863%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Azure%20Data%20Factory%20team%20is%20excited%20to%20announce%20a%20new%20update%20to%20the%20ADF%20data%20wrangling%20feature%2C%20currently%20in%20public%20preview.%20Wrangling%20in%20ADF%20empowers%20users%20to%20build%20code-free%20data%20prep%20and%20wrangling%20at%20cloud%20scale%20using%20the%20familiar%20Power%20Query%20data-first%20interface%2C%20natively%20embedded%20into%20ADF.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fpower-query%2Fpower-query-what-is-power-query%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EPower%20Query%3C%2FA%3E%20provides%20a%20visual%20interface%20for%20data%20preparation%20and%20is%20used%20across%20many%20products%20and%20services.%20With%20Power%20Query%20embedded%20in%20ADF%2C%20you%20can%20use%20the%20PQ%20editor%20to%20explore%20and%20profile%20data%20as%20well%20as%20turn%20your%20M%20queries%20into%20scaled-out%20data%20prep%20pipeline%20activities.%20Data%20Flows%20in%20ADF%20and%20Synapse%20Analytics%20will%20now%20focus%20on%20Mapping%20Data%20Flows%20with%20a%20logic-first%20design%20paradigm%2C%20while%20the%20Power%20Query%20interface%20will%20enable%20the%20data-first%20wrangling%20scenario.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3C%2FP%3E%3CDIV%20class%3D%22video-embed-center%20video-embed%22%3E%3CIFRAME%20class%3D%22embedly-embed%22%20src%3D%22https%3A%2F%2Fcdn.embedly.com%2Fwidgets%2Fmedia.html%3Fsrc%3Dhttps%253A%252F%252Fwww.youtube.com%252Fembed%252FhhfGAzSp-zo%253Ffeature%253Doembed%26amp%3Bdisplay_name%3DYouTube%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253DhhfGAzSp-zo%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252FhhfGAzSp-zo%252Fhqdefault.jpg%26amp%3Bkey%3Db0d40caa4f094c68be7c29880b16f56e%26amp%3Btype%3Dtext%252Fhtml%26amp%3Bschema%3Dyoutube%22%20width%3D%22302%22%20height%3D%22170%22%20scrolling%3D%22no%22%20title%3D%22YouTube%20embed%22%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorMark%20Kromer_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EWith%20Power%20Query%20in%20ADF%2C%20you%20now%20have%20a%20powerful%20tool%20to%20use%20in%20your%20ADF%20ETL%20projects%20for%20data%20profiling%2C%20data%20prep%2C%20and%20data%20wrangling.%20You%20have%20immediate%20feedback%20from%20introspection%20of%20your%20Lake%20and%20database%20data%20with%20the%20Power%20Query%20M%20language%20available%20for%20your%20data%20exploration.%20You%20can%20then%20take%20your%20resulting%20mash-up%20and%20save%20it%20as%20a%20first-class%20ADF%20object%20and%20orchestrate%20a%20data%20pipeline%20with%20that%20same%20M%20Power%20Query%20executing%20on%20Spark.%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorMark%20Kromer_1%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22power-query-activity.png%22%20style%3D%22width%3A%20346px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248374iC31B925204454E94%2Fimage-dimensions%2F346x213%3Fv%3D1.0%22%20width%3D%22346%22%20height%3D%22213%22%20role%3D%22button%22%20title%3D%22power-query-activity.png%22%20alt%3D%22power-query-activity.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20have%20completed%20your%20data%20exploration%2C%20save%20your%20work%20as%20a%20Power%20Query%20object%20and%20then%20add%20it%20as%20a%20Power%20Query%20activity%20on%20the%20ADF%20pipeline%20canvas.%20With%20your%20Power%20Query%20activity%20inside%20of%20a%20pipeline%2C%20ADF%20will%20execute%20your%20M%20query%20on%20Spark%20so%20that%20your%20activity%20will%20automatically%20scale%20with%20your%20data%20by%20leveraging%20the%20ADF%20data%20flow%20infrastructure.%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorMark%20Kromer_2%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22power-query-pipeline.png%22%20style%3D%22width%3A%20531px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248373i973D54C337D65560%2Fimage-dimensions%2F531x96%3Fv%3D1.0%22%20width%3D%22531%22%20height%3D%2296%22%20role%3D%22button%22%20title%3D%22power-query-pipeline.png%22%20alt%3D%22power-query-pipeline.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20example%20above%2C%20I%20added%20my%20Power%20Query%20activity%20to%20my%20pipeline%20for%20cleaning%20addresses%20from%20my%20ingested%20Lake%20data%20folders%20with%20Power%20Query%2C%20then%20handing%20the%20results%20off%20to%20a%20Data%20Flow%20via%20ADLS%20Gen2%2C%20where%20I%20perform%20data%20deduplication%20and%20then%20use%20the%20ADF%20pipeline%20to%20send%20emails%20when%20the%20process%20completes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBecause%20you%20are%20in%20the%20context%20of%20an%20ADF%20pipeline%2C%20you%20can%20define%20destination%20sinks%20for%20your%20Power%20Query%20mash-up%20so%20that%20you%20can%20persist%20the%20results%20of%20your%20transformations%20to%20data%20store%20like%20ADLS%20Gen2%20storage%20or%20Synapse%20Analytics%20SQL%20Pools.%20Leverage%20the%20power%20of%20ADF%20to%20define%20source%20and%20destination%20mappings%2C%20database%20table%20settings%2C%20file%20and%20folder%20options%2C%20and%20other%20important%20data%20pipeline%20properties%20that%20data%20engineers%20need%20when%20building%20scalable%20data%20pipelines%20in%20ADF.%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorMark%20Kromer_4%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fwrangling-tutorial%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EClick%20here%3C%2FA%3E%20to%20learn%20more%20about%20Azure%20Data%20Factory%20and%20the%20power%20of%20data%20wrangling%20at%20cloud%20scale%20with%20the%20new%20updated%20Power%20Query%20public%20preview%20feature%20in%20ADF.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2084863%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20Data%20Factory%20had%20updated%20the%20data%20wrangling%20feature%20public%20preview.%20Power%20Query%20is%20now%20available%20as%20a%20top-level%20factory%20resource%20and%20activity%20for%20data%20exploration%20and%20data%20prep%20at%20scale.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22pq03.png%22%20style%3D%22width%3A%20483px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248360i55FE5259ECC9B67F%2Fimage-dimensions%2F483x258%3Fv%3D1.0%22%20width%3D%22483%22%20height%3D%22258%22%20role%3D%22button%22%20title%3D%22pq03.png%22%20alt%3D%22pq03.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2084863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Wrangling%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2092362%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Data%20Factory%20Enables%20Data%20Wrangling%20at%20Scale%20with%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2092362%22%20slang%3D%22en-US%22%3E%3CP%3ENice!!%3C%2FP%3E%3CP%3EWhat%20is%20it%20using%20under%20the%20hood%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Jan 24 2021 12:49 AM
Updated by: