Project, Research

How to Use MS Flow to Refresh a PowerBI Dataflow and a PowerBI Dataset upon completion

This blog will cover step by step instructions on how to make a Microsoft Flow that will refresh a PowerBI Dataflow, and after completion of the dataflow refresh it will refresh a PowerBI Dataset.

1. Register new Azure App

Image for post

2. Retrieve Dataflow Workspace ID and Dataflow ID

  1. Navigate to your dataflow to refresh
  2. Click “…” options and select “Settings”
  3. URL will display the workspace ID first and the dataflow ID second

3. Create PowerBI Dataflow Refresh Flow Custom Connector

  • Set up connector:
    1. Navigate to https://us.flow.microsoft.com/en-us/
    2. Select “Custom connectors” under the “Data” section
    3. Click “New custom connector” and “Create from blank”
    4. Make a name and under General tab fill out the description and make the host “api.powerbi.com”
  • Security tab:
    1. Select OAuth 2.0 as authentication type
    2. Azure Active Directory as Identity Provider
    3. Enter your Client ID and Client Secret from the Azure App in Step 1
    4. Login URL: https://login.windows.net
    5. Tenant ID: common
    6. Resource URL: https://analysis.windows.net/powerbi/api
    7. Scope: openid
    8. Redirect URL: will be blank until connector is made. After connector is made we will return to add this to the Azure app
  • Test your custom connector:
    1. Create “New connection”
    2. Enter the parameters for your workspace ID and dataflow ID (from previous Step)
    3. Click “Test Operation”

4. Create PowerBI Dataflow Status Flow Custom Connector

  • Set up connector:
    1. Create new blank connector
    2. Use the same security settings as the step above
  • Definition:
    1. Create a new action
      • Summary: Retrieve Dataflow Refresh Status
      • Description: Retrieve Dataflow Refresh Status
      • Operation ID: RetrieveStatus
    2. Create a new request
    3. Click “Update connector” at top of page
  • Add Redirect URL to Azure App if necessary (mine was the same as the first connector)
  • Test the custom connector like the step above to ensure it is working

5. Build Microsoft Power Automate Flow

The following flow will refresh a dataflow and check its refresh status every 1 minute until it is complete, after which it will call the refresh of a dataset.

  • Step 1 – Recurrence: Dictates the frequency and interval of flow running
  • Step 2 & 3-Set variables for Workspace ID & Dataset ID
    • Copy and paste in your workspace and dataflow IDs into the “values” section from previous steps to customize the flow to your needs
    • Make sure Type is set to “String”
  • Step 4 & 5 – Initialize variables for Initial Dataflow refresh time & Latest Dataflow refresh time
    • These need to be initialized so they can be set later with values
    • Do not enter any values, but set type to “String”
  • Step 6 – Run custom connector to get the Initial Dataflow Refresh Status
    • Pass in the variables set above for WorkspaceID and DataflowID
  • Step 7 -Parse JSON response from the Initial Dataflow Refresh Status
    • For schema go to end of Post that says “JSON Schema for JSON Responses” and copy/paste the Schema section
    • If that Schema no longer works, go to your custom connector test, copy the “response” from the test, click “Generate from sample”, and paste the response in there to automatically generate the Schema
    • Set the Content to the “body” from the step Retrieve Initial Dataflow Refresh Status
  • Step 8 – Use “Apply to each” Flow steps to set the Initial Dataflow Refresh time variable
  • Step 9 – Refresh PowerBI Dataflow using the custom connector
    • Pass in the variables for WorkspaceID and DataflowID
  • Step 10 – Build the “Do Until” step that checks if the Dataflow refresh time has been updated before moving on to refresh the Dataset
  • Step 10A – Do Until section:
    • Set the condition to the variables “Latest Dataflow Refresh Time” is not equal to “Initial Dataflow Refresh Time”
    • Remove the “Count” under “Change limits” section
    • Change Timeout if necessary (PT1H means 1 hour, use Google to find other valid values)
  • Step 10B -Use custom connector to retrieve the latest dataflow refresh status
    • Pass in the variables for WorkspaceID and DataflowID
  • Step 10C – Parse the JSON response the same way as the previous time (using same Schema as before)
    • This time make sure the Content “body” is coming from the step “Retrieve Latest Dataflow Refresh Status”
  • Step 10D – Set Latest Dataflow Refresh Time variable using “Apply to each” (same as setting the Initial Dataflow Refresh Time variable above)
  • Step 10E – Delay 1 minute before running the check again
  • Step 10 Complete – The completed “Do Until” step should look like this:
  • Step 11 – Run the PowerBI dataset refresh flow step

The completed flow will look as follows:

JSON Schema for JSON Responses

{
    "type": "object",
    "properties": {
        "name": {
            "type": "string"
        },
        "description": {
            "type": "string"
        },
        "version": {
            "type": "string"
        },
        "culture": {
            "type": "string"
        },
        "modifiedTime": {
            "type": "string"
        },
        "pbi:mashup": {
            "type": "object",
            "properties": {
                "fastCombine": {
                    "type": "boolean"
                },
                "allowNativeQueries": {
                    "type": "boolean"
                },
                "queriesMetadata": {
                    "type": "object",
                    "properties": {
                        "Encompass Sales 5 Years - Azure": {
                            "type": "object",
                            "properties": {
                                "queryId": {
                                    "type": "string"
                                },
                                "queryName": {
                                    "type": "string"
                                },
                                "loadEnabled": {
                                    "type": "boolean"
                                }
                            }
                        },
                        "paramDatabase": {
                            "type": "object",
                            "properties": {
                                "queryId": {
                                    "type": "string"
                                },
                                "queryName": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                },
                "document": {
                    "type": "string"
                }
            }
        },
        "annotations": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "name": {
                        "type": "string"
                    },
                    "value": {
                        "type": "string"
                    }
                },
                "required": [
                    "name",
                    "value"
                ]
            }
        },
        "entities": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "$type": {
                        "type": "string"
                    },
                    "name": {
                        "type": "string"
                    },
                    "description": {
                        "type": "string"
                    },
                    "pbi:refreshPolicy": {
                        "type": "object",
                        "properties": {
                            "$type": {
                                "type": "string"
                            },
                            "location": {
                                "type": "string"
                            }
                        }
                    },
                    "attributes": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "name": {
                                    "type": "string"
                                },
                                "dataType": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "name",
                                "dataType"
                            ]
                        }
                    },
                    "partitions": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "name": {
                                    "type": "string"
                                },
                                "refreshTime": {
                                    "type": "string"
                                },
                                "location": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "name",
                                "refreshTime",
                                "location"
                            ]
                        }
                    }
                },
                "required": [
                    "$type",
                    "name",
                    "description",
                    "pbi:refreshPolicy",
                    "attributes",
                    "partitions"
                ]
            }
        }
    }
}

Leave a Reply