getting started with data factory

[db042190]

hi, i thought i'd start learning a bit about data factory by trying to do something i want instead of what some of the tutorials show.  so shown below i believew i've used what i understand is a studio to create a factory.  what i'd like to do now is to use some power query code that i used elsewhere as a source of data.  and land that data on prem in sql.  i suppose if i had to land it in the cloud that would be ok instead.  but i dont see much in the way of flexibility here.  how would i start?  the power query has its origins in a python script that extracts stock values from yahoo finance.  can data factory be used to do something like this without getting ssis involved?

[Shay2023]

It sounds like you're looking to use Azure Data Factory to automate the process of extracting data using Power Query (M code) and then load that data into an on-premises SQL Server or potentially in the cloud. Azure Data Factory (ADF) is a cloud-based data integration service that allows you to create, schedule, and manage data pipelines for various data movement and transformation tasks. It's primarily used for ETL (Extract, Transform, Load) processes.

While Azure Data Factory is quite flexible, it might not be the best tool for running Power Query directly, as Power Query is typically used within tools like Power BI or Excel. However, you can achieve your goal using a combination of tools and services. Here's a high-level approach:

1. Data Extraction and Transformation:

   Since your data originates from Yahoo Finance and is processed through Power Query (M code), you might want to consider the following steps:

   • Create a script (Python or another suitable language) to extract data from Yahoo Finance.
   • Use Power Query in Power BI Desktop to process and transform the extracted data. You can create a query in Power BI using M code and apply the transformations you need.

2. Data Loading:

   Once you have the transformed data, you can proceed with loading it into your target database:

   • Use Azure Data Factory to create a data pipeline.
   • In the pipeline, you can have a Copy Data activity where you specify the source as the output of your Power Query transformation in Power BI.
   • For the destination, you can choose to land the data in an on-premises SQL Server or in the cloud-based SQL database, depending on your preference.

3. Azure Data Factory Mapping Data Flows (Optional):

   If you need more complex transformations beyond what Power Query can handle, you can use Azure Data Factory's Mapping Data Flows feature. This provides a visual interface for building data transformation logic without coding.

It's important to note that while Azure Data Factory can handle the data movement and orchestration part, Power Query itself might not be directly integrated into ADF. If you have complex transformations in your Power Query script, you may need to refactor or reimplement those transformations using the tools available in Azure Data Factory.

In summary, Azure Data Factory can help you automate the process of moving data between sources and destinations, but the direct integration of Power Query might require additional steps and considerations. Depending on your specific use case, you might also explore other Azure services like Azure Logic Apps or Azure Functions to handle the data transformation and orchestration aspects.

[db042190]

thx shay2023.  the output of my pbi pq is essentially a pbix file but is also part of a dataset known to the pbix and of course the pbi service after the pbix is published. 

So i have to ask how either one of those can be plumbed to azure data factory as a source.  I must be missing something.  I dont believe published ds's can even be seen by adf but am not sure.

[db042190]

thx all. i guess i'll start a new post where you suggested with a link back here. 

and explain here (again) that i already know what i should see, otherwise i wouldn't have posted here.  and that i dont want to follow the e2e just yet as the most fundamental part of this product (PQ) isnt even working yet for my license trial. 

ms is looking for a tech who can help here.  we'll know tomorrow if they found someone.  i'll open up a ticket if that doesnt work out.   

notice below that neither the trial nor the core fabric radio buttons are available in advanced properties in a new workspace.

[db042190]

I'm going to try and post the adf part of this question at https://techcommunity.microsoft.com/t5/azure-data-factory/ .  I think i'm getting confused amongst all the forums.  As far as i know i've always posted pbi questions to the same community forum (https://community.powerbi.com/ ) but they started getting redirected to the fabric community automatically at one point.  So naturally I started posting df questions here too.   All very very confusing.  Pls try to answer just the fabric part of this df question.  Something tells me all the problems i'm having with power automate ( https://community.fabric.microsoft.com/t5/Desktop/error-when-attempting-to-use-power-automate-in-des... ), azure df, fabric df, pl300 exam prep labs that require a github command prompt instead of a button (documented but never answered satisfactorily at https://trainingsupport.microsoft.com/en-us/mcp/forum/mcp_exams-mcp_prep-mcp_mooc/while-taking-pl300... ), unsolicited/billable 365 installs ( https://answers.microsoft.com/en-us/msoffice/forum/msoffice_other-msoffice_unknown-mso_subother/ms-i... ) are all related but nobody really knows (or cares) what's going on and the consumer is stuck with nowhere to turn for what is probably one problem manifesting in multiple products.

[db042190]

so here is what i am running into...

1) if i follow GracerGu's suggested link, i immediately find that in the power bi service there is no default icon as mentioned there but that is just the beginningh of my issues i think ("Select the default Power BI icon at the bottom left of the screen, and switch to the Data Factory experience.")

2) if instead of going thru the power bi service like Grace's link suggests, i continue trying to create the pipeline i showed in the first post of this thread, and i basically attempt to create a new power query as shown in the 1st image below, i dont get the experience described at https://learn.microsoft.com/en-us/azure/data-factory/control-flow-power-query-activity , where i am supposed to be presented with the ability to create a dataset as in other products like power bi desktop.  see 2nd image for the experience i'm supposed to get.

3) i'm surprise my existing pbi datasets dont show in the ds drop downs for adf.  i published one to a workspace other than "my workspace" and it didnt make a difference.  i'm not seeing any truth to the advertising claim/implication that this is all integrated.

[GraceGu]

The experience you pasted  https://learn.microsoft.com/en-us/azure/data-factory/control-flow-power-query-activity is in Azure Data Factory product only. 

In Fabric Data Factory, Power Query experience is in dataflow refer to Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn.

data source managment and connector types supported refer to Data source management - Microsoft Fabric | Microsoft Learn and  Connector overview - Microsoft Fabric | Microsoft Learn.

[db042190]

thx, so if we focus for a minute on azure data factory , why is it that im not seeing the ability to have the power query building experience shown at  https://learn.microsoft.com/en-us/azure/data-factory/control-flow-power-query-activity  ?   is this the wrong forum for azure data factory?   which is correct?  frankly the fabric thing and which forum is which is quite confusing.   when i go to the service (using this url https://app.powerbi.com/home ) i end up here https://app.powerbi.com/home?experience=power-bi .

and if we now focus on your fabric link, do i need to set up an account different from my azure tenant in order to follow the example there?  If not, why dont i see the pbi icon or df experience in the pbi service?  What is meant by "Switch to the Data factory or Power BI experience"?  from where?  and can i try this part of the fabric product out for free?  will it replace azure df?

i assigned a data factory contributor role to myself in the azure portal and see nothing still.  When i tried the link at https://learn.microsoft.com/en-us/fabric/get-started/fabric-trial#start-the-fabric-preview-trial to try the preview trial i land in pbi instead of where that learning item says i should.  when i launch the df studio from the portal, i land in my df instance but again with no capability of a power query creation experience like the one i get in pbi desktop etc.   can someone pls help.

[GraceGu]

For Azure Data Factory questions, use Newest 'azure-data-factory' Questions - Stack Overflow

For PQ experience, recommended to start from Fabric dataflow. You can check the prerequisites in Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn

@miguel for further comments if have. And move this thread to Dataflows - Microsoft Fabric Community 

[miguel]

Just moved this to the dataflows subforum.

@db042190 once you enable the Microsoft Fabric Trial, you should be able to see a workload selector on the bottom left corner which might have the icon of POwer BI by default. When you click on it you'll see all available workloads where you can pick the one for Data Factory. Below is a screenshot to showcase this:

Once you click on Data Factory you should be able to click on "New" and be able to see the Dataflow Gen2 artifact as well as the Data Pipeline for the Data Factory Workload.

Highly encourage you to give our E2E tutorial a try from the link below:

Data Factory end-to-end scenario - introduction and architecture - Microsoft Fabric | Microsoft Lear...

Let us know if this helps. Happy to answer other related questions to Data Factory in Microsoft Fabric.

[GraceGu]

Can Data Factory end-to-end scenario tutorial help?

[db042190]

no, that looks like all the rest of the tutorials where a standard off the shelf data source is used.  As i stated i want to plug in a scripted data source (either python or power query generated in pbi from what was originally python) .  from what i saw in the data factory advertising, power query is supposed to be compatible.

[mllopis]

Generally speaking, you should be able to take your Power Query M code generated in Power BI Desktop, or Excel, or other Power Query integrations and create a Dataflow Gen2 in Data Factory in Fabric with it.

There are a number of exceptions to the general case though:

1. Some data connectors might not be supported. Refer to List of all Power Query connectors - Power Query | Microsoft Learn for more details.
2. Some data transformations might not be suported. There are very few on this bucket, but particularly R & Python scripts created in Power Query within Power BI Desktop are not supported in other hosts, including Data Factory in Fabric.

Hope that helps - Per your earlier comments, if your PQ queries in Power BI Desktop are fully created in PQ (e.g. not copy-pasted Python code), you likely should be able to make those work in a Dataflow Gen2 and leverage Output Destinations currently supported: Azure SQL Databases, Fabric Lakehouse/Warehouse/KQL.