By connecting Lawlift and Microsoft Power Automate, it is now also possible for you to use the business intelligence tool Power BI. For this purpose, you can make metadata available via Lawlift and prepare it in attractive dashboards.

How to set up the connection and make the data available for Power BI is described below:

1. Create a dataset:

A dataset is a database that holds the required data and forms the basis for Power BI. In order to create such a dataset and make it accessible for the JSON files generated later, you have to log in with your Microsoft user account at microsoft.com. 

On the right-hand side, you will now define the structure of the dataset inside the field “Body”. To do this, copy the content of your generated JSON file from Lawlift and paste it here. The exported JSON file can look like this, for example:

{"PowerBI HR Dashboard": {
	"email":"john@doe.com",
	"Firstname":"John",
	"Lastname":"Doe",
	"Gender":"Female",
	"Residence":"Berlin",
	"Salary":2500,
	"Startdate":"December 01, 2023",
	"Minijob":"No",
	"Probationary_period":"Yes",
	"Field_of_application":"Job title",
	"Place_of_activity":"Place"
	}
}

You now need to make adjustments so that the file corresponds to the correct format for Power BI. To do this, you must specify a name for the dataset and the table and define the data types of the respective columns. LAWLIFT only outputs numeric variables ("Int64" and "double") and character strings ("string") as data types, which is why only these data types can be defined for the columns.

To get the JSON file into the correct format, you also need to add square brackets. The following JSON file shows how the example from before must be converted.

{
 "name": "LAWLIFT BI PA ENG",
 "defaultMode": "Push",
 "tables": [ 
   {
     "name": "HRSuiteENG",
     "columns": [
       {
        "name": "Firstname",
        "dataType": "string"
       },
       {
        "name": "Lastname",
        "dataType": "string"
       },
       {
        "name": "Salary",
        "dataType": "Int64"
       },
       {
        "name": "Residence",
        "dataType": "string"
       },
       {
        "name": "Gender",
        "dataType": "string"
       },
       {
        "name": "Startdate",
        "dataType": "string"
       },
       {
        "name": "Minijob",
        "dataType": "string"
       },
       {
        "name": "Probationary_period",
        "dataType": "string"
       },
       {
        "name": "Field_of_application",
        "dataType": "string"
       },
       {
        "name": "Place_of_work",
        "dataType": "string"
       },
       {
        "name": "email",
        "dataType": "string"
       }
      ]
    }
  ]
}

The JSON file describes the dataset. You can now create the dataset in Power BI using the "Run" button. If this was successful, you will see 201 as the response code:

If an error occurs, there may be a problem with the structure of the dataset required by Microsoft. More information about the structure of JSON datasets can be found here.

Note: You can only use datasets that have been created in this way via the API and are accessible to JSON files. Datasets created via the PowerBI user interface cannot be used!

2. Create the workflow in Power Automate:

To do this, go into your Power Automate user interface and create a new flow (see How do I create a Power Automate flow). Select the Lawlift Export Trigger and add "Parse JSON" as the first step. To create the schema, please refer to this help article. 

The same JSON file was used here as was used to create the dataset in Power BI. This is not absolutely necessary. However, in the next step, we would like to import the metadata created via our publication into Power BI and need the corresponding data from the data tab for this.
                

3. Add Power BI to your Workflow

As the next step, choose "Add rows to dataset":

You can now select your workspace from Power BI and select the dataset previously generated via API.

4. Choose PowerBI properties

In the following, you have the option of selecting the contents that you have defined in the dataset (so-called "properties") and thus including them into PowerBI:

5. Creating a Dashboard with PowerBI

Go to the website: https://app.powerbi.com. There you can now generate a new dashboard under "Create". To do this, select that you want to access a dataset that has already been created and click on the dataset that has just been created via the API, which is displayed there.

Here you can then select "Create blank report". 

It is now possible to create interactive dashboards from the generated data. The data is automatically updated each time the template is exported from Lawlift so that you are always up to date. Here you can see an example of such a dashboard. Further information on working with Power BI can be found here.