1. Help Center
  2. APIs & Webhooks

How to integrate Contify APIs with Power BI?

Contify News APIs provide dynamic updates that can be directly integrated with Microsoft Power BI for analysis and visualizing the data

You can integrate Contify News APIs with Power BI in the following steps - 

  1. Step 1: Setup an API endpoint via the Contify API developer portal
  2. Step 2: Setup the desired API endpoint as a Power BI data source (Data extraction)
  3. Step 3: Setup Power BI queries to transform and load the API data (Data transformation and loading)

Step 1: Setup an API endpoint via the Contify API developer portal

  1. Login to the Contify API developer portal with your credentials
  2. Generate an API endpoint by passing the desired IDs or an advanced query
  3. The generated API endpoint (Request URL) will look something like this -
    https://api.contify.com/e/v2/insights?<your query parameters> 
  4. Take note of your API keys - Application ID (APPID) and Application KEYS (APPSECRET) - You can find them in the API developer portal here

Step 2: Setup the desired API endpoint as a Power BI data source

  1. Launch Power BI desktop - Do note that Power BI desktop is not available for Mac OS
  2. Select 'Web' as a source present in the 'Get Data' dropdown under the 'Home' tab
  3. Under the 'advanced' option, input the API endpoint URL and your APPID and APPSECRET, and click 'OK'

  4. Power BI will load the data as a 'Source' in Power BI query editor. The query will look something like this - 
    = Json.Document(Web.Contents("https://api.contify.com/e/v2/insights?

    <your query parameters>",

    [Headers=[APPID="cfyxxxxxxxx", APPSECRET="xxxxxxxxxxxxxcfy"]]))
  5. The API data extraction is complete

Step 3: Setup Power BI queries to transform and load the API data

  1. Contify API response is paginated, with each page containing 20 updates - To extract data for each page of Contify API response, you will need to write some queries in Power BI query editor
  2. In the 'Source' query which extracts Contify API data to Power BI, you need to tell Power BI that the API response is paginated, so that each page updates can be appended together to create the desired dataset
    1. Update the 'Source' query by adding &page=1-2 in the query as follows - 
      = Json.Document(Web.Contents("https://api.contify.com/e/v2/insights?

      <your query parameters>&page=1-2",

      [Headers=[APPID="cfyxxxxxxxx", APPSECRET="xxxxxxxxxxxxxcfy"]]))
    2. Next step is to transform the data into rows. Click on 'Source' and select 'insert step after'. Put this query in the editor window to transform the result set into rows - 
       = Source[results]
    3. Expand this set of rows in to table by selecting the 'convert to table' option. Expand all the columns, and rename the column header "column1.title" to "title"
  3. Now, you need to create a function in Power Query, that tells Power BI to invoke page numbers in the Contify API response. Click on the query name that is used to extract the data in Step 2, right-click on it and select 'Create function'
    1. Name the function as "GetData"
    2. The query for the function is - 
      = (Page) =>

      let

          Source = Json.Document(Web.Contents("https://api.contify.com/e/v2/insights?

      <your query parameters>&page=" & Number.ToText(Page),

      [Headers=[APPID="cfyxxxxxxxx", APPSECRET="xxxxxxxxxxxxxcfy"]]))

      in

      Source
  4. Now, click on the query name that is used to extract the data in Step 2, right-click on it and select 'reference'. Name the referenced dataset as "AllData", and use the following query as the 'Source' for this dataset - 
    = List.Generate(

        () => [id = 1, title = GetData(0)],

        each not List.IsEmpty([title][results]) ,

        each [id = [id] + 1, title = GetData([id])],

      each [title])
  5. The above query loops through all the paginated results of Contify API response, and appends them together to create the entire dataset
  6. Expand this set of rows in to table by selecting the 'convert to table' option. Expand all the columns
  7. You should see the entire data set loaded in Power BI Query Editor
  8. Click on 'Close and Apply' to save the changes in Power BI Query Editor and load the entire Contify API response dataset in Power BI 
  9. The dataset is now ready to be analyzed and visualized

Did you know that Power BI dashboards can also be embedded in Contify platform. Click here to learn more.