Custom JSON Schema to simplify parsing of Power BI Scanner API output for Power Automate
The Power BI Scanner API is an essential part of the Power BI Admin API that enables administrators to automatically retrieve valuable information about their organization's Power BI assets. However, parsing the output JSON file can be complex and confusing with many duplicate property names, especially when using Power Automate. This blog post will show you how to parse the Power BI Scanner API's JSON output using a custom JSON schema to avoid duplicate property names and simplify flow creation.
Power BI Scanner API Output
In order to use the Power BI Scanner API, you need to run the WorkspaceInfo PostWorkspaceInfo API first, which has 5 parameters and in this blog post I used "datasetExpressions" and "datasetSchema" to get the dataset schema (tables, columns and measures) and dataset expressions (DAX and Mashup queries) [find out more about these parameters here]. The "WorkspaceInfo PostWorkspaceInfo" API returns a unique ID called scanId, which needs to be used in another API called "WorkspaceInfo GetScanResult" to get the result in the JSON format. Note: You can check the status of your scanId by using the "WorkspaceInfo GetScanStatus" API. Read this blog post to learn how to run a Power BI Rest API with Power Automate: https://www.vahiddm.com/post/call-power-bi-rest-api-with-power-automate
In Power Automate, what does Parse JSON do?
The Parse JSON action is a powerful feature in Power Automate that can help you to work with data in JSON format. It lets you take the raw JSON data obtained from sources such as HTTP requests and convert it into structured values that you can use in your flow. This feature simplifies the flow building process, making it more accessible even if you don't have advanced coding skills. In simple terms, it makes creating flows more comfortable and convenient for everyone.
To utilize the Parse JSON action, you will need to provide two essential pieces of information:
JSON data - The output of the Rest API
JSON schema - The schema of the JSON data
Creating a JSON schema is straightforward in Power Automate. Simply click on the "Generate from sample" button, paste an example JSON payload into the text box, and click "Done". The schema will be automatically generated based on the sample provided.
However, most JSON data contains duplicate property names (Id and Name) even though their path is different, which makes creating the flow difficult, and this is the problem you will encounter after parsing the Power BI Scanner API JSON output:
How to solve this problem?
To overcome this challenge, a solution is to use a custom JSON schema with annotations for "title" and "description" to avoid duplicate property names and simplify flow creation. By leveraging these annotations, the Parse JSON action can provide more detailed and easily understandable outputs.
The result is the game changer:
This game-changing approach allows IT professionals to effectively utilize the Parse JSON action within Power Automate, particularly when working with the Power BI Scanner API output. To further assist with this, a sample JSON schema for the Power BI Scanner API output, specifically for the "datasetExpressions" and "datasetSchema" parameters, can be downloaded for reference form my GIT repository: https://github.com/VahidDM/Custom-JSON-Schema-Power-BI-Scanner-API