Difference between revisions of "Excel Power Query"

From Charitylog Manual
Jump to: navigation, search
(→‎What you will need)
 
(32 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| Authentication File]].
+
[[File:helpheader_small.png|right]]
 +
 
 +
[[API Details| Go to API overview]]
 +
 
 +
__TOC__
 +
 
 +
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.  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 Read/Write)
 +
*Microsoft Excel 2016 or later
 +
*API Development tool (Optional)
 +
 
 +
=Setting up our first API query=
 +
 
 +
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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| 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.
 
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.
Line 5: Line 21:
 
[[File:API_exceldatatab.png]]
 
[[File:API_exceldatatab.png]]
  
−
Please see [[Adding MS Excel Data Tab]] for further details.
+
 
  
  
Line 14: Line 30:
 
This will open the Power Query Editor and create Query 1.  We are going to use the advanced editor to paste the following:
 
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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| Authentication File]].
 
*Details of the authentication, stored in our [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| Authentication File]].
−
*A call to the dictionary to see which records we have on the system.
+
*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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| Authentication File]].  In the advanced editor delete the details currently displayed and paste the example below from the text file, making sure you have updated the keyFile line..
 +
 
 +
[https://dizions.sharepoint.com/:t:/s/externalsite/Eau8xvyhgC1Njk5SxZSiJR0Bkg6hMHzHh0fKFsN7Wn4RGA Org List text]
 +
 
 +
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 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'.
 +
 
 +
From here we can click on close and load.  If you haven't set the privacy above to will be prompted to do so.  The data that is received from the API will then be added to a new worksheet in a table.  If we wish to edit the query we can right click on the query in the 'Queries and Connections panel".  If you have minimised the panel in can be found on the Data tab of the ribbon.
 +
 
 +
The next steps from here is to add further queries to your workbook.
 +
 
 +
=Moving forward=
 +
In this section you will find examples of queries to help you move forward.  Each query will need to be added to the header information as explain earlier.
 +
 
 +
This example text file below will, making sure you have updated the keyFile line.:
 +
 
 +
*Retrieve records from type 1 (the default client record of the system)
 +
*The fields retrieved will be the Record ID, Surname, Forenames and ethnicity.
 +
*The query will convert to a table and expand the fields to include the fields above.
 +
 
 +
[https://dizions.sharepoint.com/:t:/s/externalsite/EWSgB5SBm59OmO-kzPXhoMMBDt2UFXrKCo3L1bsW_BX4Mg Client Query text]
 +
 
 +
This example text file below will, making sure you have updated the keyFile line.:
 +
 
 +
*Retrieve referrals from all projects form 01/01/2020 to 31/12.2020
 +
*The fields retrieved will be the Referral/Case ID, the date, contact method, client age range at time of referral and the name of the project..
 +
*The query will convert to a table and expand the fields to include the fields above.
 +
 
 +
[https://dizions.sharepoint.com/:t:/s/externalsite/EXKDtVS6BwdFuAwoucIxRcoBNQv2rLcY5RkPSDL4lB8zgg Referral Query Text]
  
−
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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| Authentication File]].  In the advanced editor delete the details currently display and paste the example below, making sure you have updated the keyFile line.
+
You can download the below XLSX file that has three queries;
 +
*Record Types
 +
*All Referrals in 2020
 +
*Record Type 1 (Default Client Record)
  
−
let
+
Once opened you will need to edit each query to specify the location of your [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query_(Authentication_File)| Authentication File]].  This is done by editing the keyFile line.
−
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],
 
−
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'.
+
[https://dizions.sharepoint.com/:x:/s/externalsite/EfqycM7wpP5EgsYzSE0xEPgB5azftri7Xdat5rImmbk8xw Example with 3 queries.xlsx]
  
−
Example query
+
[[File:helpheader_small.png|right]]
−
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"
 

Latest revision as of 12:28, 7 February 2024

Helpheader small.png

Go to API overview

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. 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 Read/Write)
  • Microsoft Excel 2016 or later
  • API Development tool (Optional)

Setting up our first API query

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.

API exceldatatab.png



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

API getdata.png

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 displayed and paste the example below from the text file, making sure you have updated the keyFile line..

Org List text

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

From here we can click on close and load. If you haven't set the privacy above to will be prompted to do so. The data that is received from the API will then be added to a new worksheet in a table. If we wish to edit the query we can right click on the query in the 'Queries and Connections panel". If you have minimised the panel in can be found on the Data tab of the ribbon.

The next steps from here is to add further queries to your workbook.

Moving forward

In this section you will find examples of queries to help you move forward. Each query will need to be added to the header information as explain earlier.

This example text file below will, making sure you have updated the keyFile line.:

  • Retrieve records from type 1 (the default client record of the system)
  • The fields retrieved will be the Record ID, Surname, Forenames and ethnicity.
  • The query will convert to a table and expand the fields to include the fields above.

Client Query text

This example text file below will, making sure you have updated the keyFile line.:

  • Retrieve referrals from all projects form 01/01/2020 to 31/12.2020
  • The fields retrieved will be the Referral/Case ID, the date, contact method, client age range at time of referral and the name of the project..
  • The query will convert to a table and expand the fields to include the fields above.

Referral Query Text

You can download the below XLSX file that has three queries;

  • Record Types
  • All Referrals in 2020
  • Record Type 1 (Default Client Record)

Once opened you will need to edit each query to specify the location of your Authentication File. This is done by editing the keyFile line.

Example with 3 queries.xlsx

Helpheader small.png
Retrieved from ‘https://wiki.dizions.co.uk/index.php?title=Excel_Power_Query&oldid=16433’