Help

Page tree

Power BI Integration

Easily export  Agiloft data to Power BI for analysis by setting up a Power BI direct connector for your KB. 

Prerequisites

This integration requires the Advanced or Premium edition of Agiloft.

To set up the connector:

  1. Open Power BI.
  2. Go to the Power Query Editor.
  3. Click New Source and select Blank Query.
  4. Open the Advanced Editor.

  5. Paste the following code block in the editor:

    REST API
    let 
    loginUrl = "https://example.agiloft.com/ewws/EWLogin?$login=PowerBI&$password=PASSWORD&$lang=en&$KB=example_kb",     
    loginRequestBody = "{""KB"":""example_kb"",""lang"": ""en""}",
    loginHeaders = [#"Content-Type"="application/json"],
    loginOptions = [Headers=loginHeaders, Content=Text.ToBinary(loginRequestBody)],
    loginResponse = Web.Contents(loginUrl, loginOptions),
    loginJson = Json.Document(loginResponse),
    accessToken = loginJson[access_token],
    apiUrl = "https://example.agiloft.com/ewws/alrest/example_kb/example_table/search?lang=en",
    body="{""query"": ""id>1"", ""field"": [""id"",""created_by"",""updated_by"",""contract_amount"",""contract_start_date"",""contract_end_date"",""internal_contract_owner"",""wfstate""]}",
    headers = [#"Accept"="application/json", #"Content-Type"="application/json", #"Authorization"="Bearer " & accessToken],
    options = [Headers=headers, Content=Text.ToBinary(body)],
    apiResponse = Web.Contents(apiUrl, options),
    source = Json.Document(apiResponse, 65001),
    result = source[result],
    headers2 =
     let
        allHeaders = List.Combine(List.Transform(result, Record.FieldNames)),
        uniqueHeaders = List.Distinct(allHeaders)
     in
        uniqueHeaders,
        resultsTable = Table.FromRecords(result, headers2, MissingField.UseNull),
    #"Changed Type" = Table.TransformColumnTypes(resultsTable,{ {"id", Int64.Type}})
in
    #"Changed Type"

  6. In the Advanced Editor, replace the placeholder values in the code:

    1. In the loginUrl value:

      1. Replace the first segment with the URL of your KB. This should begin with https:// and end with EWlogin.

      2. Replace PowerBI and PASSWORD with the credentials for the user account you will use to pass the data to Power BI. Make sure the account has permission to read the data that will be passed to Power BI, and is part of at least one group that has permission to use REST services (Setup > System > Manage Web Services).

      3. Replace example_kb with your KB name. The name is located in the top right-hand corner of your KB near the Help icon.

      5. Then, go to Setup > Access > Automatic Login Hotlinks and use the Encrypt Hotlink section to encrypt the URL. Replace the entire example URL in the quotation marks with the encrypted URL you generated.
      6. Make sure the line includes quotation marks at the beginning and end of the URL, and a comma after the final quotation mark.

    2. In the loginRequestBody value, replace example_KB with the name of your KB. 

    3. In the apiUrl value, replace the following:
      1. Replace the first segment with the URL of your KB, just like you did in step 6a.
      2. Replace example_KB with the name of your KB.
      3. Replace example_table with the logical name of the table you want to import data from. For example, contract. Be sure to include search?lang=en" after the table name, as shown in the example above.
    4. In the body value, you can determine which fields you'd like to import from the table. Simply replace the example placeholders with the logical names of the fields you'd like to use. For example, contract_amount. You can change the number of fields by adding or removing code to this section. Make sure you always include an ID field.
  7. Click Done.
  8. Repeat the process above beginning at step 3 until you've added all the table data you want to use in Power BI.
CONTENTS