Power Query Tutorial#

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.

Configure your Excel Workbook#

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 named Excel Cell

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]

Get the value of a named Cell

Create a function to retrieve all Documents from your Project#

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

EXCEL_2022-08-29_08-20-26.gif

= () => 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.

EXCEL_2022-08-29_08-21-54.gif

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")]]))

EXCEL_2022-08-29_08-27-54.gif

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.

EXCEL_2022-08-29_08-32-05.gif

Review the data in Excel#

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

2022-08-29_08-34-57.gif

Press refresh to update your Excel#

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

EXCEL_2022-08-29_08-37-35.gif

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

EXCEL_2022-08-29_08-38-28.gif

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.

EXCEL_2022-08-29_08-40-59.gif

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

Extract documents automatically and review Documents in Excel

Contact our support if you want to receive the Excel file.