Difference between revisions of "Excel Power Query"

Revision as of 16:09, 24 July 2020

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.

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.

Please see Adding MS Excel Data Tab for further details.

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 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.

let
keyFile = "C:\Users\YourName\Desktop\Dizions_api_access.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/dictionary/client_type",
apiResults = Json.Document(Web.Contents(URL, [Headers=[#"Source"=SourceKey, #"Org"=OrgKey, #"User"=UserKey]]))
in
    apiResults