Power BI refreshes in one place with Power Automate (Flow)

Power Automate has two unique approaches to interacting with Power BI. First, is event induced actions, where a float starts running if a defined fee is exceeded. This interaction is only the trigger for a glide. Read extra about that here. Second, there are also Power BI related movements to be had in Power Automate. For the controlled refresh waft, we want a Power BI motion to be brought on based totally on other settings.

Currently, the local Power BI actions are in preview, but additionally, a piece limited, to be honest. The local moves most effective support the following operations:

Trigger a Power BI dataset refresh

Add rows to a Power BI dataset.

Looking at our use case, we want to do lots greater than the handiest triggering a dataset to refresh. For example, dataflows can’t be brought about to refresh primarily based on these movements. To solve this challenge, we created a custom connector, which permits us to do the whole thing the Power BI REST API supports.

In Power Automate, you can create HTTP calls for every API call you need to do as properly. However, this requires a lot of extra work (and rework), on every occasion you want to call this API. The custom connector can help to preserve our flows easy and smooth and re-use API calls in different flows as properly. Ida Bergum wrote a remarkable weblog on growing a custom connector for Power BI API calls in Power Automate.

Based on this custom connector setup, we can be able to name a whole lot of different activities, which can be supported using the Power BI REST API. Besides the dataset, we can call a dataflow refresh as well now.

Centrally manage refresh schedules

Since we determined to have one central area to manage all our Power BI refreshes, we need to save the schedules somewhere. The simplest answer for this is to create a SharePoint listing for that.

Looking at the input values for the Power BI REST API, we need at the least the groupId (workspace) and the dataflow or dataset Id for you to cause the refresh.

Besides that, we have to save the refreshed agenda as well. Since one in every one of our goals is, to have one central place to store our schedules, we set up a SharePoint listing with the following columns:

• Title, free text to provide some extra context to the object represented.
• workspace, a workspace where the dataflow or dataset is a component of.
• ItemType, dropdown to choose if the object represented is a dataflow or dataset.
• ItemId, the Id of the dataflow or dataset, based totally upon ItemType.
• RefreshSchedule, listing of hours when the refresh needs to be triggered.

Power Automate setup

In Power Automate, everything will be brought together. In short, we need to examine all gadgets inside the SharePoint listing, evaluate the schedules with the cutting-edge time and if needed, cause the refresh based totally on the previously set Power BI REST API custom connector. In this setup, we can schedule the glide to run each hour, to trigger the refresh for a dataflow or dataset each hour if scheduled for that time. Follow for more at Power BI Course

Check scheduled refresh hours

There are a couple of methods in Power Automate to retrieve the modern hour. In this instance, we can use the motion to convert a certain time from timezone. This is carried out on purpose so that we are 100% sure that we cause it based totally on our local time zone.

The beneath setup will run each hour and return me the first characters of the cutting-edge time. For example, if it's far 16.39 (4.39 PM), it's going to go back 16. In the following steps, we want the output of this step to define which gadgets wish to be refreshed.

Get agenda and split based on dataflow or dataset

Next up, is getting the SharePoint list inside the glide. We will do this with the motion Get items from a SharePoint listing. This will return all items inside the previously created list as a JSON object.

ManageRefreshes3

Since the list of controlled refresh gadgets can come to be very long, we filter it down to the simplest of the objects that need to be brought on. This will result in a smaller set of items and performs higher inside the relaxation of our float. Because the returned cost of the preceding step is a JSON, we can use the operation Filter array for this step.

ManageRefreshes4

In this step we used the Contains filter because likely there are a couple of hours listed within the column RefreshSchedule, representing a couple of hours that the specified object desires to be refreshed. The result set of this step is a filtered set of objects based on the current hour. The column RefreshSchedule is filtered through the result of our in advance step in which we converted the time.

Next, we want to Parse the JSON object. This will help us to make the JSON higher readable within the subsequent steps and have all the objects available within the next steps. Please run the float once, to generate an instance output from the Filter step and insert this as a sample to outline the JSON schema. Please, reproduction and paste the result of the check run also to a notepad for later use.

ManageRefreshes5

The ensuing JSON would possibly look something like the beneath example.

[
  {
  "@odata.etag": "\"5\"",
  "ItemInternalId": "73",
  "ID": 73,
  "Title": "[ItemTitle]",
  "OData__x0072_p24": "[WorkspaceId]",
  "ItemType": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 0,
  "Value": "[ItemType]"
  },
  "ItemType#Id": 0,
  "twiu": "[ItemIdFromList]",
  "f6h8": "17",
  "Modified": "2020-01-12T16:02:20Z",
  "Created": "2020-01-12T15:27:45Z",
  "Author": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Claims": "i:0#.f|membership|[YourEmail]",
  "DisplayName": "[YourName]",
  "Email": "[YourEmail]",
  "Picture": "[SharePointUrl]",
  "Department": "[DepartmentName]",
  "JobTitle": [JobTitle]
  },
  "Author#Claims": "i:0#.f|membership|[YourEmail]",
  "Editor": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Claims": "i:0#.f|membership|[YourEmail]",
  "DisplayName": "[YourName]",
  "Email": "[YourEmail]",
  "Picture": "[SharePointUrl]",
  "Department": "[DepartmentName]",
  "JobTitle": null
  },
  "Editor#Claims": "i:0#.f|membership|[YourEmail]",
  "{Identifier}": "[SharePointListUrl]",
  "{IsFolder}": false,
  "{Thumbnail}": {
  "Large": null,
  "Medium": null,
  "Small": null
  },
  "{Link}": "[SharePointUrl]",
  "{Name}": "[ItemTitle]",
  "{FilenameWithExtension}": "[ItemTitle]",
  "{Path}": "Lists/[SharePointListName]/",
  "{HasAttachments}": false,
  "{VersionNumber}": "1.0"
  }

]

If you want to learn Power BI, then Getting Started with Power BI Training is an excellent course to start with.