How to transform Azure SQL data using Power Query from Power Automate

We'll get to know the techniques

Create Manually Executed Flow (Instant Cloud Flow):

1. Access Power Automate.
2. Select "Create" and choose "Automated Flow (Snapshot)".
3. Configure the parameters and steps that will be performed when the flow is manually triggered.

Power Query Data Transformation step:

1. Add a data transformation step using the "SQL Server" connector.
2. Select the Transform Data action, then choose the table or data you want to transform.

(Choose Data, Database, Table):

1. Choose the Azure SQL Database and the specific table you want to transform.

Applied Steps, Query Folding, and SQL Scripting:

1. In Power Query, you can apply various transformations and query folds to manipulate the data.
2. Example of text to JSON transformation:

Let
    Source = Sql.Databases("sua_conexao_sql"),
    Database = Source{[Name="sua_base_de_dados"]}[Database],
    Table = Database{[Name="sua_tabela"]}[Table],
    ColumnTransform = Table.TransformColumns(Table, {{"coluna_texto", Json.FromText}}),
In fashion
    TransformColumn

1. Use the Power Query transformation to convert text to JSON format for records.

Diagram View:

1. In Power Automate, you can view the step diagram to understand the logic of your flow.

, Dynamic Content):

1. Add a "SQL Server" step and choose the "Insert Row" action.
2. Map the table columns with the dynamic content from the previous step.

Query result in Tabular format (

1. Add another "SQL Server" step and choose the "Select" action.
2. Configure the SQL query to get the results you want.

Keep in mind that the construction of the flow can vary depending on the complexity of the data and the transformations you want to apply. The examples provided are simplified and may need to be adjusted as needed.