This article explains how to load Pigment data in Microsoft PowerBI, specifically how to easily use Pigment’s API with the Get data from Web feature of PowerBI.

Use the Pigment API to retrieve Pigment data, this can be queried directly from PowerBI. Follow the steps below to quickly set up the API.

Step 1 - Prepare API credentials

To prepare the API credentials:

1. Go to Workspace Settings.
2. Select API Keys.
3. Select + Create API key in the top-right corner of the page to create a new key.
4. Enter an API key name, expiration date and toggle Key type to Export.

Save the generated key as you will need it to authenticate the data request from PowerBI.

The key tells Pigment which Workspace is targeted, and if the request is made by an authorized user with the appropriate security permissions.

Step 2 - Find the Block or View ID you want to load

To configure the API endpoints, we need a few identifiers to reference Blocks and Views.

You will typically find these elements in the URL of the page, i.e. when you navigate to a Block in Pigment you will have this kind of URL in your browser:

https://pigment.app/w/company-name/application/023a8e40-a9a0-4daf-bced-eabc08af93ce/tables/933e36d0-d032-45dd-affc-b7412abd1beb?viewId=f1f3edd7-e639-4701-9fce-fe7f16fb4379&displayMode=Grid

Note the /tables/ part of the URL: it can take the value of Lists (for Transaction Lists and Dimension Lists), Metrics and Tables. This is the Block type. 

• The part of the URL after tables/ is the ID of the Block currently displayed.
• The part after viewId= is the ID of the currently displayed View (it stops before the &).

As a summary, the example above contains the following values:

• Block type: tables
• Block ID: 933e36d0-d032-45dd-affc-b7412abd1beb
• View ID: f1f3edd7-e639-4701-9fce-fe7f16fb4379

Take note of the information you need (Block type and Block ID or just the View ID)

Step 3 - Prepare the API endpoints

Block

If you want to use a Block, then you need to build your API endpoint this way:

https://pigment.app/api/export/{blockType}/{blockId}

and replace both blockType and blockId with the values saved in the previous step.

Using our example, it looks like this:

https://pigment.app/api/export/table/933e36d0-d032-45dd-affc-b7412abd1beb

Notice how “tables” became “table”. In API endpoints, Block types are always singular.

View

https://pigment.app/api/export/view/{viewId}

Replace both blockType and blockId with the values saved in the previous step.

Using our example, it looks like this:

https://pigment.app/api/export/view/f1f3edd7-e639-4701-9fce-fe7f16fb4379

Step 4 - Load Pigment data from PowerBI

Now that you have prepared everything, it’s very easy to configure the import in PowerBI (desktop version).

From the desktop PowerBI application, select Get data.

Select Other, then choose Web. 

Select the Advanced mode and add the details below.

In URL parts, you can either put the full API endpoint or split it like in the example below to make it easier to update the IDs later.

In HTTP request header parameters, add the following information:

• Header name: Authorization
• Header value: Bearer {apiKey} (replace {apiKey} by the key you generated in step 1)
  Make sure “Bearer” is written exactly this way and there is a space between this and the key value like below.

Select OK, then follow the usual import steps from PowerBI.