You can integrate Contify News APIs with Power BI in the following steps -
Step 1: Setup an API endpoint via the Contify API developer portal
Step 2: Setup the desired API endpoint as a Power BI data source (Data extraction)
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
Login to the Contify API developer portal with your credentials
Generate an API endpoint by passing the desired IDs or an advanced query
The generated API endpoint (Request URL) will look something like this -
https://api.contify.com/e/v2/insights?<your query parameters>
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
Launch Power BI desktop - Do note that Power BI desktop is not available for Mac OS
Select 'Web' as a source present in the 'Get Data' dropdown under the 'Home' tab
Under the 'advanced' option, input the API endpoint URL and your APPID and APPSECRET, and click 'OK'
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"]]))The API data extraction is complete
Step 3: Setup Power BI queries to transform and load the API data
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
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
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"]]))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]
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"
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'
Name the function as "GetData"
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
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])The above query loops through all the paginated results of Contify API response, and appends them together to create the entire dataset
Expand this set of rows in to table by selecting the 'convert to table' option. Expand all the columns
You should see the entire data set loaded in Power BI Query Editor
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
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.