Difference between revisions of "Power BI with Power Query"

From Charitylog Manual
Jump to: navigation, search
 
(22 intermediate revisions by 2 users not shown)
Line 8: Line 8:
 
*API Development tool (Optional)
 
*API Development tool (Optional)
  
−
=Creating a Queary=
+
=Creating a Query=
  
 
Here we are going to look at how to get started using the API and Power Query in Power BI.
 
Here we are going to look at how to get started using the API and Power Query in Power BI.
Line 15: Line 15:
  
 
[[File:PowerBI-getdata.png]]
 
[[File:PowerBI-getdata.png]]
 +
  
 
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.
 
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.
Line 21: Line 22:
  
  
−
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.  On the menu ribbon in PowerBi Desktop select the advanced editor, to display the code editor.
−
*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 displayed and paste the text from the file below, making sure you have updated the keyFile line.
+
[[File:PowerBI-AEribbon.png]]
  
−
[https://dizions.sharepoint.com/:t:/s/externalsite/EXKDtVS6BwdFuAwoucIxRcoBNQv2rLcY5RkPSDL4lB8zgg Referral Query Text]
+
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
 +
      URL = "https://api.dizions.co.uk/v2/clients/selectable_fileds?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.  
 
You will be prompted that Information is required for the data privacy.  
Line 33: Line 42:
 
Click the check box to ignore privacy and select save.
 
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;
+
The query will then run and display a preview table of the fields available from the client endpoint.
−
*id - Case/Referral ID
+
 
−
*date - The recorded date of the case/referral
+
[[File:CFtable.png]]
−
*client - This is the client (record) ID
+
 
−
*referrer - This is the named referrer recorded.
+
=Creating a Query to call data from the client endpoint=
−
*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.
+
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.
−
*project_name - This is the name of the project that the case/referral is recorded against.
+
 
 +
In the query editor select New Source on the ribbon and then blank query.
 +
 
 +
[[File:QEblankquery.png]]
 +
 
 +
On the ribbon in select the advanced editor, to display the code editor.
 +
 
 +
[[File:PowerBI-AEribbon.png]]
 +
 
 +
In the advanced editor we are going to create a query to get client ID's and names.
 +
 
 +
[[File:CFqeury2.png]]
 +
 
 +
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.
 +
 
 +
[[File:CQtableexpand.png]]
 +
 
 +
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.

Latest revision as of 11:46, 11 September 2023

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.

PowerBI-getdata.png


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.

PowerBI-blankquery.png


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.

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:

CFqeury.png

Copy below 

let
     URL = "https://api.dizions.co.uk/v2/clients/selectable_fileds?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.

CFtable.png

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.

QEblankquery.png

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

PowerBI-AEribbon.png

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

CFqeury2.png

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.

CQtableexpand.png

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.

Retrieved from ‘https://wiki.dizions.co.uk/index.php?title=Power_BI_with_Power_Query&oldid=15803’