This guide provides a walkthrough for integrating Power BI with Langdock. By following the steps below, you create a functional assistant that responds to data-related queries using DAX. In the example, the assistant is able to answer question about an Airbnb listings dataset.

​

Prerequisites:

• Langdock account
• PowerBI account
• Access to the Azure Portal with rights to create an App registration

​

Create an App registration in Azure

1. Navigate to your Azure Portal https://portal.azure.com/and click “App registrations”

2. Add a new app registration by clicking “New registration”

3. Name your application (e.g. “Langdock Actions Integration”

4. Select “Web” under Redirect URI and set the URL to https://app.langdock.com/api/actions/callback

5. Register your app by clicking “Register”

6. Under “Manage” > “API permissions”, click “Add a permission”

7. Find “Power BI Service” from the catalog

8. Select “Delegated permissions”

9. Add the permissions you need for your use-case. E.g. for querying data from datasets, select the “Dataset.Read.All” permission.

The required scopes for each PowerBI API Endpoint are documented in the Power BI REST APIs documentation.

10. Click “Add permissions”

​

Connect an assistant to PowerBI

11. In Langdock, go to Assistants and create a new assistant. Give it a name, e.g. “PowerBI Assistant”.

12. Under instructions, add the following prompt:

ALWAYS use DAX queries and NEVER use SQL queries (they will fail). When needed: You can use two actions after each other to 1) understand the data, 2) answer the user's question.

13. Add a new action by clicking “New action”

14. Copy and paste the following OpenAPI Schema:

openapi: 3.0.1
info:
  title: Power BI API
  description: API for executing queries on Power BI datasets.
  version: 1.0.0
servers:
  - url: https://api.powerbi.com/v1.0/myorg
paths:
  /datasets/{datasetId}/executeQueries:
    post:
      summary: Execute queries on a Power BI dataset
      operationId: executeQueries
      parameters:
        - name: datasetId
          in: path
          required: true
          schema:
            type: string
          description: The ID of the dataset to query.
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                queries:
                  type: array
                  items:
                    type: object
                    properties:
                      query:
                        type: string
                        description: The DAX query to execute. Always in a DAX format.
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: object
                description: The response from the Power BI API.
        '401':
          description: Unauthorized
        '403':
          description: Forbidden
        '404':
          description: Dataset not found
      security:
        - bearerAuth: []
components:
  securitySchemes:
    bearerAuth:
      type: http
      scheme: bearer
      bearerFormat: JWT

15. Select “OAuth” as an Authentication method

16. Go back to your Azure Portal and under “Certificates & secrets”, create a new client secret, by clicking “New client secret”

17. Add a description and click “Add” to create the secret

18. Copy the newly created secret

19. Go back to Langdock and paste the secret into the “Client Secret” field.

20. In your Azure Portal, go to “Overview” and copy your “Application (client) ID”

21. In Langdock, paste the value into the “Client ID” field

22. In your Azure Portal, click on “Endpoints” under “Overview”

23. Note the “OAuth 2.0 authorization endpoint (v2)” and “OAuth 2.0 token endpoint (v2)” values

24. Paste these values into the matching fields in Langdock

25. Add the scopes defined earlier in the following format: https://analysis.windows.net/powerbi/api/Dataset.Read.All

(Replace Dataset.Read.All with other values if needed)

26. Select “Default” as a Token Exchange Method

27. Click “Switch to reusable url” to make sure the Redirect URL is https://app.langdock.com/api/actions/callback

​

Add information about your data

28. Open PowerBI and find the IDs of all the Datasets you would like to use in your assistant

29. One way to find out the ID of a dataset is to copy the semantic model link by clicking “Share” > “Copy semantic model link” > “Copy” and finding the ID in the copied URL.

E.g. if the URL is https://app.powerbi.com/datahub/datasets/c9c45bb4-060e-41da-a762-80b2f4eb5bd5?ctid=ee973eb7-3390-40ea-a258-2e453dcd61cf&pbi_source=linkShare the ID can be found after .../datahub/datasets/ and before ?ctid..., so in this example it’s c9c45bb4-060e-41da-a762-80b2f4eb5bd5

30. Back in Langdock, you should describe your datasets in the assistant instructions.

Make sure to provide the assistant with the ID of the dataset, a short description and the structure of the data. Tip: you can take a screenshot about an overview of your dataset in PowerBI and let Langdock write a description for you.

In our example, these are the final instructions of our assistant:

ALWAYS use DAX queries and NEVER use SQL queries (they will fail).
When needed: You can use two actions after each other to 1) understand the data, 2) answer the user's question.

Available datasets:

# Airbnb Dataset
- ID: c9c45bb4-060e-41da-a762-80b2f4eb5bd5
- Comment: Airbnb Dataset, use this if the user doesn't specify anything
- Structure:
- Tables:
  - AB_NYC_2019:
      Columns:
        - name: availability_365
          type: integer
        - name: calculated_host_listings_count
          type: integer
        - name: host_id
          type: integer
        - name: host_name
          type: string
        - name: id
          type: integer
        - name: last_review
          type: date
        - name: latitude
          type: float
        - name: longitude
          type: float
        - name: minimum_nights
          type: integer
        - name: name
          type: string
        - name: neighbourhood
          type: string
        - name: neighbourhood_group
          type: string
        - name: number_of_reviews
          type: integer
        - name: price
          type: integer
        - name: reviews_per_month
          type: float
        - name: room_type
          type: string

​

Test your assistant

31. You are ready to test your assistant. Ask a question that the assistant should be able to answer by querying your dataset.

In our example a question could be: “What are the most expensive neighborhoods?”

32. You will be asked to authenticate to continue, click “Sign in with api.powerbi.com”

33. You will see the requested permissions and will be asked to accept the request. After this you will be redirected to the assistant editor.

Note: it is possible that your conversation is not visible anymore, don’t worry, this will not happen to the users of the assistant, it’s only a limitation of the assistant builder.

34. You can preview the request that the assistant is about to send, by clicking on the name of the action (in this case, executeQueries on "api.powerbi.com").

35. Click “Allow” (or “Always Allow”) to approve the query.

36. That’s it! The assistant executed the query and replied with an answer based on your data from PowerBI.