All Collections
Public API
How to connect Microsoft PowerBI with Craft.io
How to connect Microsoft PowerBI with Craft.io
Roni Ben-Aharon avatar
Written by Roni Ben-Aharon
Updated over a week ago

Craft.io has a set of APIs which exposes a lot of data about your workspace’s working items. You can bring this data into your database or directly into your visualization tool.

Here we will show you how to connect to Craft.io APIs and bring your workspace’s data into Microsoft Power BI.

(*) It's important to note that this integration is a suggested approach and may require customization based on your specific requirements. Craft.io does not guarantee seamless out-of-the-box support for every use case. Users are advised to use these suggestions at their own discretion, considering their unique system configurations and requirements.😊

🎥 Watch video tutorial

Pre-requirements

To import your workspace data into Power BI via an API request, it's essential to have the following information readily available:

  • Your Craft.io account ID

  • Your Craft.io workspace IDs

  • Your account's API Key

To obtain this type of information, please reach out to your Customer Success Manager. They will be able to provide you with the necessary details.

Power BI

Bring in the tables!

  1. In a new Power BI Desktop file, click on “Get data” > Web
    ​
    ​

    A screenshot of a computer Description automatically generated


    ​

  2. Choose “Advanced” option and then enter the details:

    1. URL parts: https://api.craft.io/workspace/<workspace_id>/items?fields=<list> &limit=<number>

    2. Accept: application/json

    3. x-api-key: The API key from Pre-requirements
      ​

      A screenshot of a computer Description automatically generated


      ​

  3. A “Power Query Editor” will open. Click on “List” > “To Table” > expand “Column1"
    ​


    In the window that appears, bring only the necessary columns (include “customFields”), and un-mark this option: "Use original column name as prefix".

    Name this table as “Working Items”.
    Change column types and names as needed; expand nested json object when necessary (do not expand “customeField”, we will expand it in a separate table).
    ​
    ​

  4. Repeat steps 1-3 with the following data:

    1. Create table “Map of Custom Fields”, using this URL: https://api.craft.io/workspace/<workspace_id>/custom_fields

    2. Create table “Terminology”, with this URL: https://api.craft.io/workspace/<workspace_id>/terminology
      ​

  5. How to open Terminology table:

    1. Click on "Applied Steps" → source
      ​
      ​

    2. Convert → into table
      ​


      ​

    3. Expand the "Value" column with the button on the right side of the column
      ​


      ​

  6. Create “Custom Field” table by following these steps:

    1. Right click “Working Items” table on the left panel → Reference
      ​


      ​

    2. Rename the new table “Custom Fields”

    3. Use “Choose columns” to keep only columns “id” and “customFields”
      ​

      A screenshot of a computer Description automatically generated

    4. Rename “id” to “item id”

    5. Expand “customFields” and keep all columns. pay attention that some of the less-commonly used columns might be found in "load more" when choosing which columns to expand.

    6. If you have configured in your Craft.io workspace a custom field of type multi-value picklist, you will have a "values" column available. Expand this column and choose a comma-separated delimiter

Merge and connect the tables

  1. Custom Fields table: Expand "custom fields” column to new rows, and then expand again to “id” and “value”. Add “custom field name” column from the “Map of Custom Fields” table to the “Custom Fields” table:

    1. In the table “Custom Fields”, click on Combine > Merge Query.
      ​

      A screenshot of a computer Description automatically generated

    2. Merge the two tables based on “Custom Fields” customFields.id = “Map of Custom Fields” id. Bring only the “name” column from “Map of Custom Fields” table. After doing so, you will not need the “Map of Custom Fields” table, and you can hide it from view (In Model View, click on the eye next to the table name).
      ​

      A screenshot of a computer Description automatically generated


      ​

  2. Many interesting questions and KPIs can be asked about the item’s parent, so adding parent’s data to the table can be useful. Do this by Self-join: In “Working Items” table, click on Combine > Merge Query.
    Then merge based on id = parent.id.
    ​

    A screenshot of a computer Description automatically generated


    bring only the columns you need from the Parent.
    ​

  3. Connecting the tables: “Working items”.id = “Custom Fields”.id; “Working items”.type = “Terminology”.Name

Create a report for another workspace

Do you have multiple Craft.io workspaces and want to create separate reports for each of them?

To create a report for another workspace, start by duplicating the report you've previously created in the earlier section.

In the "Transfer Data" section of the duplicated report, you'll need to make some adjustments to ensure the data is pulled from the new workspace. Follow these steps for each your tables:

  1. For each table, modify its source to match the new workspace ID (and key, if necessary).

  2. In the case of the "Custom Field" table, not only should you change the source reference table but also the merge table from which you're pulling the field "Name" (the "Map of custom field" table).
    ​
    ​

By following these steps, you can easily generate reports for multiple Craft.io workspaces, ensuring that the data is tailored to each workspace's specific requirements.

Video tutorial

Did this answer your question?