How can we improve Microsoft Azure Data Factory?

Add support for Power Query / Power BI Data Catalog as Data Store/ Linked Service

Power Query is awesome! It would be a great feature to be able to output its result into either a SQL database or Azure (Storage or SQL).

447 votes
Vote
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
You have left! (?) (thinking…)
andre shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Please check the new capability we recently unveiled called Wrangling Data Flows, available in preview! Wrangling Data Flow allows you to discover and explore your data using the familiar Power Query Online mashup editor to do data preparation, and then execute at scale using Spark runtime.

Sign up for preview access at: https://forms.office.com/Pages/ResponsePage.aspx?id=v4j5cvGGr0GRqy180BHbR9-OHbkcd7NIvtztVhbGIU9UNk5QM0dSWkFDSkFPUlowTFJMRVZUUUZGRi4u and check out more details at https://aka.ms/wranglingdfdocs

11 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
An error occurred while saving the comment
  • Anonymous commented  ·   ·  Flag as inappropriate

    Hello,

    With SSIS previewing a PowerQuery Source, does it bodes well for this feature?

    (Or would the best practice be to use SSIS in ADF for this kind of needs?)

  • Anonymous commented  ·   ·  Flag as inappropriate

    With the coming emphasis on Power Query in self-service BI, as part of the Power BI service, this seems like a natural and important additional are for connections/activities.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Update please! Power Query has become VERY popular since this idea was originally posted in 2014!

  • Frederic Forest commented  ·   ·  Flag as inappropriate

    More connectors is great, but what’s really missing for ADF to take off is a powerful, flexible in-memory transformation engine. Ideally with a nice GUI (yes, I know...) to sell it. It’s a shame because it feels like A beefed)up version of PowerQuery could do just that and we’d already have loads of people trainned for it.

    I recently designed a full-cloud bi stack for a client and I didn’t manage to convince him to use ADF because it meant performing the business logic transformations in SQL procs, which he felt were an « old-school » way of doing this and not as maintainable as a visual data flow.We’ll probabbly us the SSIS IR instead but that feels like we’re sidestepping the problem.

  • Tate Bowman commented  ·   ·  Flag as inappropriate

    In my mind, this would make ADF a killer app (service). An easy to use data cleansing framework is what ADF needs!

  • Frederic Forest commented  ·   ·  Flag as inappropriate

    I'm a long time SSIS developper who jumped on the Azure bandwagon about two years ago. Having an M (PowerQuery) engine inside ADF would change EVERYTHING for ADF. I personally enjoy ADFv2 quite a bit (and quite a bit more than V1!) but many clients are put off by the pure ELT approach. With an M activity, we could do more than lift-and-shift SSIS packages, we could replace them!

  • Ralph Kemperdick commented  ·   ·  Flag as inappropriate

    Is there an update o this topic? With the anouncement that PowerQuery is now supported in the Common Data Model, it would make a lot of sense to have Power Query also on ADF V2.

  • Casper commented  ·   ·  Flag as inappropriate

    Power Query dynamically transpiles to SQL, and is the perfect tool for selecting and migrating data to a cloud database.
    Adopting the Power BI backend interface for large scale data migrations would make ADF projects much more user friendly, without sacrificing functionality.

  • Anonymous commented  ·   ·  Flag as inappropriate

    It would be great to leverage ETL skills learned in Excel, Power BI and Analysis Services to say extract data from 100 excel files of pivoted reports with multiple header rows reformat it and export it to a data lake persistent storage area as text files. Li

  • AdminAzure Data Factory Team on UserVoice (Admin, Microsoft Azure) commented  ·   ·  Flag as inappropriate

    Thanks for the great feedback. We are working to see if we can publish the produced datasets as part of the pipelines to Power BI catalog. Also, we are working on expanding the list of connectors supported in Azure Data Factory (similar to different connectors that you have in Power Query). This will allow you to connect to different data sources apart from the current supported ones (Azure db, Azure Table, Azure Blob, On-Prem SQL Server)

Feedback and Knowledge Base