Revision as of 12:14, 5 August 2020

This section requires Power BI skills and a knowledge of how the API works. It my be useful to use an API Development tool to check the dictionaries and make test calls, Postman is a suitable tool for this. Power Query is used to call the API to retrieve the data.

What you will need

In order to follow this section you will need the following;

• Dizions API Licence (Read Only or Full)
• Microsoft Power BI Professional licence
• Power BI Desktop installed
• API Development tool (Optional)

Setting up our first API query directly in Power BI Desktop

Here we are going to look at how to get started using the API and Power Query in Power BI. Before you start you will need to create an Authentication File, with this file we can then use a standard header for all of our queries that we wish to use.

Open Microsoft Power BI Desktop and select get data.

Power BI Desktop will then open and display the Get Data Menu. By default the All source menu will be selected. Scroll to the bottom of this list and select blank query and click Connect.

This will open the Power Query Editor and create Query 1. We are going to use the advanced editor to paste the following:

• Details of the authentication, stored in our Authentication File.
• A call to the API dictionary to see which records we have on the system.

To access the Advanced editor click on the View Tab and select 'Advanced Editor'. This will open a new window. For the purpose of this example you can copy the text below but you will need to change the file path on the second line (keyFile) to the location of your Authentication File. In the advanced editor delete the details currently display and paste the example below, making sure you have updated the keyFile line.

let
keyFile = "C:\Users\RichardDuheaume\Desktop\Dizions_API_Details.xlsx",
SourceKey = Excel.Workbook(File.Contents(keyFile), null, true){0}[Data]{0}[Column2],
OrgKey = Excel.Workbook(File.Contents(keyFile), null, true){0}[Data]{1}[Column2],
UserKey = Excel.Workbook(File.Contents(keyFile), null, true){0}[Data]{2}[Column2],
APIHost = Excel.Workbook(File.Contents(keyFile), null, true){0}[Data]{3}[Column2],
URL = APIHost
 & "/v2/referrals/date_active/2010-10-01,2020-10-31/field/id;date;client;referrer;contact_method;client_age_range;project_name"
 & "?resolveIds[]=ALL",
apiResults = Json.Document(Web.Contents(URL, [Headers=[#"Source"=SourceKey, #"Org"=OrgKey, #"User"=UserKey]])),
    #"Converted to Table" = Table.FromList(apiResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "date", "client", "project_name", "contact_method", "referrer", "client_age_range"}, {"Column1.id", "Column1.date", "Column1.client", "Column1.project", "Column1.contact_method", "Column1.referrer", "Column1.client_age_range"}),
     #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",Template:"Column1.date", type datetime)
in
     #"Changed Type"

You will be prompted that Information is required for the data privacy. Click on continue. Click the check box to ignore privacy and select save.

The query will then run and display a preview table of referrals dated in 2020 with the following fields;

• id - Case/Referral ID
• date - The recorded date of the case/referral
• client - This is the client (record) ID
• referrer - This is the named referrer recorded.
• contact_method - This is the contact method recorded when the case/referral was created.
• client_age_range - This is the persons age range at the time of referral to the project.
• project_name - This is the name of the project that the case/referral is recorded against.