MS Excel Power Query

Using Microsoft Excel Power Query you can directly use the Konfuzio API.

This tutorial will show you how to connect to an API and retrieve data.

Create your secure Token

A Token allows you to connect your Excel to Konfuzio. Please create your own token an keep it secure.

Disclaimer: The password and Token in the video were deleted after the video.

Download Excel Template

We offer a pre-formatted Excel template for your convenience. Please insert your unique Token into the designated space in the Configuration tab. Also, ensure to include both the Host URL and Project ID as required.

Download the Excel here.

This will help you to review all Training and Test Documents. Read more in the next section how to use it.

Configure your own MS Excel

Configure your Excel

To connect Excel to your API endpoint you need to create two variables.

1. Define an API Token, we call this cell api_token

2. the ID of the Project you want to connect to, call this cell project_id

We advise you to create two named cells, see Excel Documentation.

Create a Token

Go to the API and create your own Token. Insert this Token into the Excel Cell named api_token.

Create a function that allows you to extract those named Cells in PowerQuery

Open Excel Power Query and use a Blank Query to create your custom function.

= (rangeName) => Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

Create a function to retrieve all Documents from your Project

You can now use the GetValue function to dynamically retrieve Documents from a Project.

= () => Json.Document(Web.Contents("https://app.konfuzio.com/api/projects/" & Number.ToText(GetValue("project_id")) & "/docs/?limit=100000", [Headers=[Authorization="token " & GetValue("api_token")]]))

You need Project access

Make sure your user has access to the Project with the ID of the Cell project_id.

Allow connect anonymously. You will provide your Token in the Request Header.

Create a Function to get all Annotations for one Document

Create a third function GetDocumentAnnotations to retrieve all Annotations from a Document.

= (document_id) => Json.Document(Web.Contents("https://app.konfuzio.com/api/projects/" & Number.ToText(GetValue("project_id")) &"/docs/" & Number.ToText(document_id) & "/annotations/?limit=10000", [Headers=[Authorization="token " & GetValue("api_token")]]))

Test your function

You can test the function if you enter the Document ID of one Document.

Download and merge the data

Get the Document Details

Create a new Query and build your own processing pipeline.

Review the data in Excel

If you close PowerQuery you will see a new Sheet in Excel that contains all the data

Press refresh to update your Excel

If you upload new documents or delete any refresh the query and you will see any changes.

Edit the connections setting if you don’t want that Excel will refresh the Query automatically or when the file is opened.

Append the Annotations per Document dynamically

You can now rename the first column to combine all Documents in your project will the Annotations of those Documents. This provides you with all the data.

Start with your Analytics

Using the Query Table you can easily start to use Pivot or Power BI to analyze:

• the confidence per Document

• Review the data

• coordinate the assinged users

• … and many more use cases