Accessing Certify Results API from Microsoft Power BI or Microsoft Excel

You can access Certify Results API from Microsoft® Power BI® or Microsoft Excel®.

To use Certify Results API from Microsoft PowerShell:

  1. Open Certify Results API Swagger page:

    http(s)://yourdomain/CertifyResultsAPI/swagger/ui/index

  2. Generate a token. For more information, see Generating a Token in Certify Results API.

  3. Add the API token to Swagger.

  4. Enter the required inputs to the end point that you intend to use in the Microsoft Power BI or Microsoft Excel Results Folders. Microsoft Excel end point is shown below.

  5. Click the Try it out! button.

  6. Verify that the response code is 200.

  1. From Swagger, copy the following information:
  2. resultsAPIToken

  3. resultsAPIResultsFolderUrl

  1. Open Microsoft Power BI or Microsoft Excel in a browser.

    Microsoft Excel: Click Data and From Web.

    Microsoft Power BI: Click Get Data and select Web.

    The Web dialog opens.

  2. Select the Advanced option.

  3. In the URL parts field, paste the resultsAPIResultsFolderUrl value.

  4. From the HTTP request header parameters (optional) field, enter token.

  5. In the second field, paste the resultsAPIToken value.

  6. Click OK.

    The Power Query Editor window opens.

  7. Under Results, click List Tools.

  8. Click the Transform tab.

  9. Click theTo Table button to expand the collection into a table.

    The To Table dialog opens.

  10. Click OK.

  11. In the Column1 header, click the Expand button.

  12. Select the columns that you want to see in the report.

  13. Click OK.

  14. Click the Expand button in other columns until all of the required columns are selected.

  15. Click OK.

  16. After all of the required columns are selected, click Close & Load.

    The Power Query Editor closes.

    In Microsoft Excel, the data is populated on a new sheet.

    In Microsoft Power BI, the selected columns appear in the Fields section, and they can be included into any visualization.

  17. Click Refresh to pull the live data.

 

Related Topics Link IconRelated Topics