Refresh Power BI / Fabric with Python : Option 1

refresh_power_bi_with_python_option_1.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "#Import necessary libraries\r\n",
        "\r\n",
        "import msal\r\n",
        "import requests\r\n",
        "import json\r\n",
        "import pandas as pd"
      ],
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.livy.statement-meta+json": {
              "spark_pool": "SparkPool01",
              "session_id": 5,
              "statement_id": 28,
              "state": "finished",
              "livy_statement_state": "available",
              "queued_time": "2022-01-05T22:24:55.4371628Z",
              "session_start_time": null,
              "execution_start_time": "2022-01-05T22:24:55.615994Z",
              "execution_finish_time": "2022-01-05T22:24:55.616342Z"
            },
            "text/plain": "StatementMeta(SparkPool01, 5, 28, Finished, Available)"
          },
          "metadata": {}
        }
      ],
      "execution_count": 28,
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Set parameters\r\n",
        "\r\n",
        "client_id = \"\" #Add your Client ID here\r\n",
        "client_secret = \"\" #Add your Client Secret here\r\n",
        "tenant_name = \"\" #Add your tenant name here\r\n",
        "workspace_id = \"\" #Add your Workspace ID here\r\n",
        "dataset_id = \"\" #Add your Dataset ID here\r\n",
        "\r\n",
        "authority_url = \"https://login.microsoftonline.com/\" + tenant_name \r\n",
        "scope = [\"https://analysis.windows.net/powerbi/api/.default\"]\r\n",
        "url = \"https://api.powerbi.com/v1.0/myorg/groups/\" + workspace_id +\"/datasets/\"+ dataset_id +\"/refreshes?$top=1\""
      ],
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.livy.statement-meta+json": {
              "spark_pool": "SparkPool01",
              "session_id": 5,
              "statement_id": 29,
              "state": "finished",
              "livy_statement_state": "available",
              "queued_time": "2022-01-05T22:24:55.4772235Z",
              "session_start_time": null,
              "execution_start_time": "2022-01-05T22:24:55.7040645Z",
              "execution_finish_time": "2022-01-05T22:24:55.8633045Z"
            },
            "text/plain": "StatementMeta(SparkPool01, 5, 29, Finished, Available)"
          },
          "metadata": {}
        }
      ],
      "execution_count": 29,
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Use MSAL to grab token\r\n",
        "\r\n",
        "app = msal.ConfidentialClientApplication(client_id, authority=authority_url, client_credential=client_secret)\r\n",
        "result = app.acquire_token_for_client(scopes=scope)\r\n",
        "\r\n",
        "#Get latest Power BI Dataset Refresh\r\n",
        "if 'access_token' in result:\r\n",
        "    access_token = result['access_token']\r\n",
        "    header = {'Content-Type':'application/json', 'Authorization':f'Bearer {access_token}'}\r\n",
        "    api_call = requests.get(url=url, headers=header)\r\n",
        "\r\n",
        "    result = api_call.json()['value']\r\n",
        "    \r\n",
        "    df = pd.DataFrame(result, columns=['requestId', 'id', 'refreshType', 'startTime', 'endTime', 'status'])\r\n",
        "    df.set_index('id')"
      ],
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.livy.statement-meta+json": {
              "spark_pool": "SparkPool01",
              "session_id": 5,
              "statement_id": 30,
              "state": "finished",
              "livy_statement_state": "available",
              "queued_time": "2022-01-05T22:24:55.6173502Z",
              "session_start_time": null,
              "execution_start_time": "2022-01-05T22:24:55.960857Z",
              "execution_finish_time": "2022-01-05T22:24:57.7772358Z"
            },
            "text/plain": "StatementMeta(SparkPool01, 5, 30, Finished, Available)"
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": "                                      requestId  ...     status\nid                                               ...           \n127702015  00eaeccc-1df5-4b66-b462-154f4d4b51c3  ...  Completed\n\n[1 rows x 5 columns]",
            "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>requestId</th>\n      <th>refreshType</th>\n      <th>startTime</th>\n      <th>endTime</th>\n      <th>status</th>\n    </tr>\n    <tr>\n      <th>id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>127702015</th>\n      <td>00eaeccc-1df5-4b66-b462-154f4d4b51c3</td>\n      <td>ViaApi</td>\n      <td>2022-01-05T22:16:47.867Z</td>\n      <td>2022-01-05T22:17:22.53Z</td>\n      <td>Completed</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ],
      "execution_count": 30,
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      }
    },
    {
      "cell_type": "code",
      "source": [
        "if df.status[0] == \"Unknown\":\r\n",
        "    print(\"Dataset is refreshing right now. Please wait until this refresh has finished to trigger a new one.\")\r\n",
        "elif df.status[0] == \"Disabled\":\r\n",
        "    print(\"Dataset refresh is disabled. Please enable it.\")\r\n",
        "elif df.status[0] == \"Failed\":\r\n",
        "    print(\"Last Dataset refresh failed. Please check error message.\")\r\n",
        "elif df.status[0] == \"Completed\":\r\n",
        "    api_call = requests.post(url=url, headers=header)\r\n",
        "    print(\"We triggered a Dataset refresh.\")\r\n",
        "else:\r\n",
        "    print(\"Not familiar with status, please check documentatino for status: '\" + df.status[0] + \"'\")"
      ],
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.livy.statement-meta+json": {
              "spark_pool": "SparkPool01",
              "session_id": 5,
              "statement_id": 31,
              "state": "finished",
              "livy_statement_state": "available",
              "queued_time": "2022-01-05T22:24:55.715021Z",
              "session_start_time": null,
              "execution_start_time": "2022-01-05T22:24:57.869367Z",
              "execution_finish_time": "2022-01-05T22:24:58.9339371Z"
            },
            "text/plain": "StatementMeta(SparkPool01, 5, 31, Finished, Available)"
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "We triggered a Dataset refresh."
          ]
        }
      ],
      "execution_count": 31,
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      }
    }
  ],
  "metadata": {
    "language_info": {
      "name": "python"
    },
    "kernelspec": {
      "name": "synapse_pyspark",
      "language": "Python",
      "display_name": "Synapse PySpark"
    },
    "kernel_info": {
      "name": "synapse_pyspark"
    },
    "save_output": true,
    "synapse_widget": {
      "version": "0.1",
      "state": {}
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}

refresh_power_bi_with_python_option_1.py

#!/usr/bin/env python
# coding: utf-8
# [Using the Power BI REST APIs](https://learn.microsoft.com/en-us/rest/api/power-bi/)
# Kudos: <https://github.com/PBI-Guy/blog/blob/main/Power%20BI%20Dataset%20Refresh%20with%20Python/Power%20BI%20Dataset%20Refresh.py>
# [How to access Power BI REST APIs programmatically](https://www.sqlshack.com/how-to-access-power-bi-rest-apis-programmatically/)

# In[28]:


#Import necessary libraries

import msal
import requests
import json
import pandas as pd


# In[29]:


#Set parameters

client_id = "" #Add your Client ID here
client_secret = "" #Add your Client Secret here
tenant_name = "" #Add your tenant name here
workspace_id = "" #Add your Workspace ID here
dataset_id = "" #Add your Dataset ID here

authority_url = "https://login.microsoftonline.com/" + tenant_name 
scope = ["https://analysis.windows.net/powerbi/api/.default"]
url = "https://api.powerbi.com/v1.0/myorg/groups/" + workspace_id +"/datasets/"+ dataset_id +"/refreshes?$top=1"


# In[30]:


#Use MSAL to grab token

app = msal.ConfidentialClientApplication(client_id, authority=authority_url, client_credential=client_secret)
result = app.acquire_token_for_client(scopes=scope)

#Get latest Power BI Dataset Refresh
if 'access_token' in result:
    access_token = result['access_token']
    header = {'Content-Type':'application/json', 'Authorization':f'Bearer {access_token}'}
    api_call = requests.get(url=url, headers=header)

    result = api_call.json()['value']
    
    df = pd.DataFrame(result, columns=['requestId', 'id', 'refreshType', 'startTime', 'endTime', 'status'])
    df.set_index('id')


# In[31]:


if df.status[0] == "Unknown":
    print("Dataset is refreshing right now. Please wait until this refresh has finished to trigger a new one.")
elif df.status[0] == "Disabled":
    print("Dataset refresh is disabled. Please enable it.")
elif df.status[0] == "Failed":
    print("Last Dataset refresh failed. Please check error message.")
elif df.status[0] == "Completed":
    api_call = requests.post(url=url, headers=header)
    print("We triggered a Dataset refresh.")
else:
    print("Not familiar with status, please check documentation for status: '" + df.status[0] + "'")