Difference between revisions of "Excel Power Query"

Revision as of 13:38, 4 August 2020

This section requires a high level of Excel 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.

Here we are going to look at how to get started using the API and Excel Power Query. 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 Excel either using an existing file or a new blank workbook. The examples below will be using a blank workbook. You will need the data tab available in Excel.

Click on the data tab and select 'Get Data' on the left hand side. Select From other Sources>Blank Query

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.

//start of header, this can be used in all queries

let
keyFile = "C:\Users\YourName\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],

//End of header-API call below

URL = APIHost
 & "v2/dictionary/client_type",
apiResults = Json.Document(Web.Contents(URL, [Headers=[#"Source"=SourceKey, #"Org"=OrgKey, #"User"=UserKey]]))
in
    apiResults

Excel may prompt you 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 list of the record types available in your system. This can be used as an index to lookup ID's for the records you may wish to use. On the right hand side you can change the name of the query, like 'List of Record types'.

Example query

URL = APIHost
 & "v2/clients/type/1/field/postcode;id;name;first_name;ethnicity"
 & "?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", "name", "first_name", "postcode", "ethnicity"},  {"Column1.id", "Column1.name", "Column1.first_name", "Column1.postcode", "Column1.ethnicity"})
in
    #"Expanded Column1"