Skip to main content
Skip table of contents

Data import with the Power BI Flow interface

General

You can retrieve data from Vertex Flow directly into Microsoft Power BI using the Power BI Flow connector. This article guides you on how to get started with the connector.

Installing Power BI and enabling the Flow interface

You can download and install the Power BI Desktop reporting software for free from Microsoft's website or the Microsoft Store. To install the Flow connector, locate the installation directory for Power BI connectors, which is typically found at C:\Users<username>\Documents\Power BI Desktop\Custom Connectors. If the directory does not exist, create it. Download the Flow connector file VertexFlowConnector.pqx from the link on the right and move it to the Custom Connectors installation directory. The connector can be enabled in two different ways, depending on the connector's certificate. See the options below:

Download the Flow connector file VertexFlowConnector.pqx from here.

Confirmation of the connection certificate for use

The Power BI Flow connector is certified with Vertex Systems Oy's SSL certificate. To establish a trusted connection, Power BI needs to be informed of the certificate's digital signature fingerprint. The fingerprint is entered into the computer's registry using a tool like Windows Registry Editor, following the instructions below. If your permissions do not allow you to modify the registry, please contact your IT support personnel responsible for your computer.

Navigate to the path HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Power BI Desktop using the Registry Editor tool and create a new "Multi-String Value" (REG_MULTI_SZ) variable named "TrustedCertificateThumbprints" if it does not already exist.

Add the Flow connector fingerprint (E763CEA755E93CC562B0CE168BD91CF58AAF2607) to the variable you created. If the variable already contains fingerprints of other connectors, separate the fingerprints with the delimiter "\0" or add the fingerprints on separate lines by right-clicking the variable and selecting the Edit function.

Once the Flow connector fingerprint is saved in the registry and the Flow connector file is placed in the Custom Connectors installation directory, the Flow connector is available for use in Power BI.

If needed, you can refer to Microsoft's documentation on the subject.

You can also automate this process using the PowerShell tool, for example with the command below. Note that the following command assumes that no other connectors are installed and will overwrite any previously trusted certificate fingerprints.

reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Power BI Desktop" /v TrustedCertificateThumbprints /t REG_MULTI_SZ /d E763CEA755E93CC562B0CE168BD91CF58AAF2607

Use of the connection without certification

If the Flow connector certificate is not validated for use, you need to enable the use of unverified and uncertified extensions in the Power BI settings.

Open the File section of the ribbon.

Go to the Settings and options tab and select the Options button.

In the Security tab, go to the Data Extensions section and select the option Allow any extension to load without validation or warning.

Once the data extension security setting has been changed to the value described above and the Flow connector file has been imported into the Custom Connectors installation directory, the Flow connector is available for use in Power BI.

Retrieving information from Flow

In Power BI, go to the Home tab of the ribbon, select the Get Data function from the Data group, and choose Add from the list...

Search for the Vertex Flow connector by typing "vertex" in the search field. Select Vertex Flow and click the Connect button.

Enter the address of the Flow server from which you want to retrieve data into the text field. With Power BI's Vertex Flow connector, you can target different data queries to different Flow servers by changing the address entered in the field as needed.

Sign in with your Flow credentials. The Power BI connector does not require specific Flow credentials, but you can create a dedicated user account for reporting purposes on the Flow server if you wish.

After the initial login, Power BI will automatically sign in to the Flow server with the credentials you provided. User credentials are saved separately for each different Flow server address, allowing you to sign in with different credentials for different Flow servers using the same Power BI connector.

  1. Select the desired action.

  2. Fill in the required input data for the action.

  3. Preview the query results by clicking the Apply button.

  4. Confirm data retrieval by selecting the checkbox next to the action.

  5. You can choose multiple actions if needed. Retrieve data by clicking the Download button.

If the preview shows unopened data structures, such as Record or List, you can open these data structures and retrieve the desired information using Power BI's query editor by selecting Transform Data.

Further processing of the requested information

If the preview shows unopened data structures, such as Record or List, you can open these data structures and retrieve the desired information using Power BI's query editor by selecting Transform Data.

Click on the column expansion button indicated in the image to view the subcolumns of that column.

Select the columns you want from the list and click OK.

The original column will be replaced with the selected columns.

The numbering according to the parts list can be found in the PARTNO column inside the extAttributes column.

After making changes, save the queries using the save button in the top left corner of the query editor or the shortcut Ctrl + S.

Queries without a graphical user interface

You can create queries through the Flow connector directly in the Power Query editor using the M scripting language. With M, you can create more versatile queries and manipulate the data you retrieve before the reporting phase. To get examples of using the Flow connector through M, first create a query using the graphical user interface of the Flow connector, then open the query in the advanced editor of the Power Query editor (button in the Query section of the Home tab).

Create reports

This section introduces the basics of creating reports to get you started.

Table

  1. Select "Table" from the Visualizations section.

  2. Open the query whose data you want to visualize in the table.

Select the desired columns from under the query. The columns will appear in the selected table.

Pie chart

Select "Pie Chart" from the Visualizations section. Open the query whose data you want to visualize in the pie chart.

Drag the column you want to visualize with the pie chart into the Values field of the visualization (marked in red in the image). Drag the column by which you want to create the legend alongside the pie chart into the Legend field of the visualization (marked in blue in the image).

If you wish, you can drag the same column into both the Legend and Values fields. In the example image, the same result could be achieved by using the column statusName in both fields. If the column status were used in both fields, the legend of the pie chart would display the raw data format of the objects' status, which with a customized status model might be just a numeric series. By using a separate column in the Legend field, you can ensure that the legend of the visualization is easily readable and understandable.

Create an automatically updating report in the Power BI Service cloud service

With the instructions in this section, you can create reports that automatically update their data from Vertex Flow on schedule using Power BI's cloud service, Power BI Service.

In summary, the steps are as follows:

  1. Install the Vertex Flow connector.

  2. Create a report using Power BI Desktop software.

  3. Save the report file (.pbix).

  4. Sign in to Power BI Service using a web browser.

  5. Install the On-premises Data Gateway.

  6. Connect the On-premises Data Gateway to Power BI Service.

  7. Upload the report file to Power BI Service.

  8. Publish the report.

  9. Schedule automatic refresh.

Unlike the free Power BI Desktop software, Power BI Service is a subscription-based product. You can review Microsoft's pricing options for the service here. If you have a Microsoft 365 subscription, Power BI Service may be included.

The On-premises Data Gateway is a tool built by Microsoft for transferring local data (such as from an on-premises database) to Microsoft cloud services (such as Power BI Service). More information is available in Microsoft's documentation.

For reports using the Vertex Flow connector to automatically update in Power BI Service, the On-premises Data Gateway must be installed on a local computer that has connectivity to both Power BI Service and the Flow server. If Power BI Service cannot connect to the computer where the gateway is installed (for example, if the computer is powered off), the report cannot be refreshed. Therefore, it is recommended to install the gateway on server hardware or another computer that is always on and preferably connected via wired internet.

Unlike the free Power BI Desktop software, Power BI Service is a subscription-based product that requires payment. You can view Microsoft's pricing options for the service here. If you have a Microsoft 365 subscription, Power BI Service may be included in it.

Even if your Flow server is accessible via the public internet (e.g., https://flow.yourcompany.com) and is not strictly a local data source, Microsoft requires that custom connectors such as the Vertex Flow connector use the On-premises Data Gateway when connecting to Power BI Service.

Prepare a report

Install the Vertex Flow connector following the installation instructions earlier in this article.

Create a report following the report creation instructions earlier in this article.

By including a Card visualization with the code

= #table(type table[Refresh=datetime], {{DateTime.LocalNow()}})

or a similar code, you can easily see when the report was last refreshed.

Sign in to Power BI Service using a web browser.

Install the local data gateway

Install the On-premises Data Gateway following Microsoft's instructions on a computer reliably accessible by Power BI Service.

The essential steps for this purpose are:

  1. Installing the Data Gateway

  2. Configuring the Data Gateway

After completing the steps above, open the Connectors tab of the On-premises Data Gateway and select the directory where the Vertex Flow connector is located. The Vertex Flow connector should appear in the list on the Connectors tab.

Enable the connector through the gateway in Power BI Service following Microsoft's instructions.

Publish the report in Power BI Service

Navigate to My workspace in Power BI Service and select New, then Upload a file. Choose your .pbix report file. Your workspace should now have three new items:

  • Report (Raportti)

  • Dataset (Tietojoukko)

  • Dashboard (Koontinäyttö)

By default, the report is only visible in your own workspace. You can share it with your colleagues in Power BI Service by following Microsoft's instructions.

You can also embed the report as an interactive HTML element directly into a webpage or intranet service. Obtain the HTML <iframe> code snippet by opening your report and selecting File > Embed report > Website or portal. Embedded reports can only be viewed by individuals with whom you've shared the report, and viewing the report requires the browser to be signed in to Power BI Service.

Schedule an automatic update

To schedule automatic refresh for your report, select the dataset associated with your report and choose Refresh > Schedule refresh. Follow Microsoft's instructions and configure the refresh to use the gateway you installed earlier.

The actual refresh time of the report likely varies slightly from the scheduled time and may differ between refreshes.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close