Answered

ETL in Power Automate?

(0) Share

Report

Posted on by BrianCCampbell

103

Hi - I have lots of power apps experience and very little power automate experience and had a question about Extract, Transform and Load.

I have a 3rd party db that I need to connect to and grab data, manipulate the data, then import it into an existing table in a SQL Azure db. Right now I have been doing this manually (all of it) and am curious about the transform portion to see if power automate is the right fit.
Currently, I load the extracted data from the 3rd party db into excel through data import, then transform using power query. Does power automate have the ability to come close to manipulating data like power query?

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

Sort by

manuelstgomes 6,625 on at

Like (0)

Report
Copy link

Link copied!

Hi @BrianCCampbell

The answer is nuanced. It depends on the transformations. In some cases, it's much simpler and others much harder.

Power Automate is not an ETL tool but it can serve your purpose by fetching the data periodically and insert the data into Excel.

Can you share an example of something you want to achieve so that I can understand better what you need?

Cheers
Manuel

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
BrianCCampbell 103 on at

Like (0)

Report
Copy link

Link copied!

Hi Manuel,

Thanks for the response. I am grabbing info from a tabular data source in csv format from our payroll platform. I was pretty sure that Power Automate could handle the fetching portion of this question from the payroll platform API, but I was hoping to find out if Power Automate could be used to manipulate the data without handing it off to Excel. The biggest thing I am doing inside Excel in Power Query is pivoting the data based on the column for the value of Pay Code and Hours The original column order is like this:

Company Code | Last Name | First Name | Position ID | Pay Date | Pay Code | Hours Regular
Over Time
Double Time
Vacation

I pivot on pay code and hours to have it broken down by employee id by date by pay code
Position ID | Pay Date | Regular | Double Time | Over Time | Vacation
hours hours hours hours

If manipulating the data like this isn't something that can be done natively within Power Automate as a series of steps do you have any recommendation on how to best achieve this without having user interaction? Does this manipulation look like something that could be done by invoking a PowerShell script via Power Automate after the data is fetched from the payroll platform? Or would it be more straight-forward to use Power Automate to fetch the data and bring it into a Table in Azure SQL Database, then have a Power Automate step to invoke a stored procedure to do the manipulation and then a final Power Automate step to (run another stored procedure?) load the data into the appropriate table in Azure SQL Database? I am pretty sure I could do this all within a SQl Server Integration Services (SSIS) package if it existed on Azure SQL Database, but that is not one of the platform options unless I am running a VM with a full-blown SQL install on it (SQL Server on Azure VM) instead of using the Azure SQL Database offering.

Thanks,

Brian

Was this reply helpful? Yes No
Verified answer

mahoneypat 1,720 on at

Like (0)

Report
Copy link

Link copied!

Have you looked at the Power Query action in Power Automate? If your 3rd party db is SQL Server or Azure SQL, you can use it.
Power Automate | Microsoft Power Query

If not, it is also possible to export data from Power Query (in Excel or Power BI) to flow with an http post. Please see this article.
Export data from Power BI using Microsoft Flow – The BIccountant

Regards,
Pat

Regards,
Pat

Was this reply helpful? Yes No
BrianCCampbell 103 on at

Like (1)

Report
Copy link

Link copied!

Thanks, @mahoneypat for pointing me in this direction. I didn't know that PowerQuery was available for Power Automate and checking it out it looks like I could take my existing R code in Power Query for Excel and just paste it in the advanced properties of the Power Query for Power Automate. One thing that I was not clear on and I think IO am now is that I will have to import my data from the 3rd party using a Power Automate HTTP action for a Rest API into Azure SQL Database before I can start manipulating it. I will import the data into a service table then run the Power Query for Power Automate command against that service table to manipulate it and finally import the contents of that service table into the desired data table. I was thinking originally of trying to do all the data manipulation before importing it into Azure SQL Database, but it seems that much like @manuelstgomes pointed out, performing transform actions against a data set natively in Power Automate isn't really what it was designed to do (to the extent for what I am trying to do).

Unrelated but relevant to this question is the fact that searching for Power Query and Power automate or Flow doesn't really return the Power Query for Power Automate items since there is a ton of content for either Power query, Flow, or Excel. I can appreciate the MS branding being consistent, but I don't know if I would have stumbled across Power Query for Power Automate without you mentioning it directly.

Thanks again both for your help.

Brian

Was this reply helpful? Yes No
jrl9110 2 on at

Like (0)

Report
Copy link

Link copied!

@BrianCCampbell I would be interested to hear the solution you ended up with as I would like to know if it's possible for a similar use case.
Thanks

Was this reply helpful? Yes No