Latest revision as of 15:39, 14 August 2024

Go to API overview

This section requires Power BI skills, developer skills and a knowledge of how an API works. It may 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)

Creating a Query

Here we are going to look at how to get started using the API and Power Query in Power BI.

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. On the menu ribbon in PowerBi Desktop select the advanced editor, to display the code editor.

In the advanced editor we are going to create a query to get the available fields from the client endpoint. Below you will see what is required to get this information:

Copy below

let
     URL = "https://api.dizions.co.uk/v2/clients/selectable_fields?resolveIds=[]ALL",
     apiResults = Json.Document(Web.Contents(URL, [headers=[#"Source"="Enter_Key", #"Org"="Enter_Key", #"User"="Enter_Key"]]))
in
     apiResults

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 the fields available from the client endpoint.

Creating a Query to call data from the client endpoint

Here we are going to look at a second query. This query is going to call data from the client end point. This query will use objects to make the query easier to read and amend. We will also see how we can enter comments in a query.

In the query editor select New Source on the ribbon and then blank query.

On the ribbon in select the advanced editor, to display the code editor.

In the advanced editor we are going to create a query to get client ID's and names.

You will then be displayed a list of records. From here we can then convert this to a table. Right click on the header called list and select 'To table'. On the following screen click ok. This will then create a table. Click on the expand icon to select the required fields.

You will then be displayed the data, ready to be used in Power BI once saved.

From this point you can start building your report dashboard within Microsoft Power BI.

Please note that we are unable to provide support on building Power BI reports, this product it supported by Microsoft.

@@ Line 1: / Line 1: @@
-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.
+[[API Details| Go to API overview]]
-=Setting up our first API query=
+This section requires Power BI skills, developer skills and a knowledge of how an API works.  It may 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.
-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.
+=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)
-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.
+=Creating a Query=
-[[File:API_exceldatatab.png]]
+Here we are going to look at how to get started using the API and Power Query in Power BI.
+Open Microsoft Power BI Desktop and select get data.
+[[File:PowerBI-getdata.png]]
-Click on the data tab and select 'Get Data' on the left hand side.  Select From other Sources>Blank Query
+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.
-[[File:API_getdata.png]]
+[[File:PowerBI-blankquery.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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| 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 [[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.
+This will open the Power Query Editor and create Query 1.  On the menu ribbon in PowerBi Desktop select the advanced editor, to display the code editor.
- //start of header, this can be used in all queries
+[[File:PowerBI-AEribbon.png]]
+In the advanced editor we are going to create a query to get the available fields from the client endpoint. Below you will see what is required to get this information:
+[[File:CFqeury.png]]
+Copy below
   let
- keyFile = "C:\Users\YourName\Desktop\Dizions_API_Details.xlsx",
+      URL = "https://api.dizions.co.uk/v2/clients/selectable_fields?resolveIds=[]ALL",
- SourceKey = Excel.Workbook(File.Contents(keyFile), null, true){0}[Data]{0}[Column2],
+      apiResults = Json.Document(Web.Contents(URL, [headers=[#"Source"="Enter_Key", #"Org"="Enter_Key", #"User"="Enter_Key"]]))
- 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
+      apiResults
+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.
-You will be prompted that Information is required for the data privacy.
+The query will then run and display a preview table of the fields available from the client endpoint.
-*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'.
+[[File:CFtable.png]]
-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.
+=Creating a Query to call data from the client endpoint=
-The next steps from here is to add further queries to your workbook.
+Here we are going to look at a second query.  This query is going to call data from the client end point.  This query will use objects to make the query easier to read and amend.  We will also see how we can enter comments in a query.
-=Moving forward=
+In the query editor select New Source on the ribbon and then blank query.
-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 query below will:
+[[File:QEblankquery.png]]
-*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.
- URL = APIHost
+On the ribbon in select the advanced editor, to display the code editor.
-  & "v2/clients/type/1/field/postcode;id;name;first_name;ethnicity"
-  & "?resolveIds[]=ALL",
+[[File:PowerBI-AEribbon.png]]
- 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),
+In the advanced editor we are going to create a query to get client ID's and names.
-     #"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"
+[[File:CFqeury2.png]]
-This example query below will:
+You will then be displayed a list of records.  From here we can then convert this to a table.  Right click on the header called list and select 'To table'.  On the following screen click ok.  This will then create a table. Click on the expand icon to select the required fields.
-*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.
- URL = APIHost
+[[File:CQtableexpand.png]]
-  & "/v2/referrals/date/2020-01-01,2020-12-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",{{"Column1.date", type datetime}})
- in
-     #"Changed Type"
-You can download the below XLSX file that has three queries;
+You will then be displayed the data, ready to be used in Power BI once saved.
-*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 [[API_Authentication#Using_Excel_to_store_credentials_for_Power_Query| Authentication File]].  This is done by editing the keyFile line.
+From this point you can start building your report dashboard within Microsoft Power BI.
-[https://dizions.sharepoint.com/:x:/s/externalsite/EfqycM7wpP5EgsYzSE0xEPgB5azftri7Xdat5rImmbk8xw Example with 3 queries.xlsx]
+Please note that we are unable to provide support on building Power BI reports, this product it supported by Microsoft.

Difference between revisions of "Power BI with Power Query"

Latest revision as of 15:39, 14 August 2024

What you will need

Creating a Query

Creating a Query to call data from the client endpoint

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation and Menus

User Guides

Admin Guides

System Extras

Support

Tools

Extras