How to Load and Transform Data Tables via the Power BI Custom Connector

This article discusses Power BI Custom Connector Integration and its basic settings.

After setting up the connection with Precoro API, you can start importing data from Precoro to Power BI. To do this, use the Navigation window.

From here, you can select what tables you would like to load into your Query Editor. Click on the checkboxes to choose the ones you need to build the report.

For most tables, you can specify the data period and choose whether or not to add Document Custom Fields and Item Custom Fields columns to the current table.

As you can see, there are two representations for each object, by IDN and list. If you select the Purchase Order by IDN then you will see that the IDN table contains information on the item, while the List table contains general information on the document without item-specific details.
The IDN table allows you to display additional CFI and filter them. The List table, on the other hand, is limited to CFD.

For data period fields (createDate[left_date], createDate[right_date]), set the time range of documents to be loaded into the report based on their Creation Date. For instance, if you set 01/01/2022 as the start date and 04/04/2022 as the end date, you will receive a data set with documents created within this date range.

Please note the following:

• If you do not set createDate[left_date], all available historical data for this table will be loaded.

• If you do not set createDate[right_date], today’s date will be set up automatically.

In the cnt_preview_pages you will see the number of downloaded pages with the byList method for further byIDN integration. If the parameter is not filled out, then by default only one page will be loaded for preview.

In the cnt_preview_IDNs you will see the number of integrated IDNs from the preview pages indicated above. If the parameter is not filled out then the preview will show the first five results by default.

As soon as you click Close&Apply, the connector will load all the data of the specified sample into the model dataset.

You can also filter and download only those documents that have been created or edited since a specific date. This information can be specified in the approvalDate or modifiedSince parameter.

For Document Custom Fields and Item Custom Fields (where applicable), choose the “TRUE” or “FALSE” option. While set as “TRUE,” an additional DocumentCustomFields or ItemsCustomFields column will be added to the end of the resulting table. You will be able to decompose it and retrieve Codes and Names for all used custom field options divided by levels.

Also, note that the resulting data table always contains other default fields with Document Custom Fields and Item Custom Fields (named dataDocumentCustomFields and dataItemsCustomFields). These fields will be loaded to the result table whether you set “TRUE” or “FALSE” and will contain only a set of custom field options IDs.

Where to find Document Custom Field columns:

Where to find Items Custom Field columns:

Press Transform Data and finetune your tables for further usage in reports.

It may take a moment, depending on the size of your database and the tables you select.

After reaching the SQL window, you can:

• Filter the list of columns based on what you need to build your reports.

• Unfold columns with nested data. These columns contain objects of Record or List types. To unfold them into additional columns (for Records) and rows (for Lists), click on the corresponding button next to the column name:

Additional detailed instructions on working with List and Record types of data can be found here.

• Provide additional data adjustments and formatting depending on your needs.

Build your reports and dashboards.

You are all set! Apply your changes and go to the report builder.

If needed, you can consult the following article to see an example of a report you can build using the Precoro data.