February 6, 2020

Measure Power Query Performance using Diagnostics Tool in Power BI

Overview:
In this article, I'll walk through on how we can measure performance of Power Query Report using the Diagnose Tool in Power BI. Microsoft has just launched this new feature in Power BI - December 2019 update.

Real-Life Use-case and Scenario:
There are the scenarios or requirements where we need to apply many operations (e.g., Change the data types, Create calculated columns, Perform some M functions etc.) within Power Query Editor.

Sometime, the performed operations take time to load the data, resulting degradation in performance.

To improve the performance, we need to identify which part of the query is taking higher amount of time. Based on that analysis, we can enhance the formula to improve Power Query performance.

So, let’s see how we can achieve this!!

Step 1:
Please make sure that Power BI Desktop is updated with the latest version (December 2019 or later).
In Power BI Desktop tool, From File, select Options and Settings and click on Options.

This will open a Preview Feature. Make sure that the feature - "Query Diagnostics" is activated.

Step 2:
Open the Power BI report for which we are facing performance issue.
From Home, select Transform data. This will open the Query Editor window.

Here, we have four steps in our query editor window.

Now, let’s check which step is taking more time to execute.

Step 3:
Go to Tools Menu. We have following 2 Group Options here.
  • Step Diagnostics
  • Session Diagnostics


Step Diagnostics:
Here, we can measure the performance of an individual step. Suppose, if our Power Query has 15 steps and we only wish to measure the performance of step #12, at that time this one is best suitable option.

Session Diagnostics:
With this option, we can measure the performance of the entire report covering all the steps of Power Query.

Here, we will perform both the actions and try to analyze the data.

Step 4:
Let’s first perform “Session Diagnostics”.
Click on “Start Diagnostics”.

Now, Click on Refresh All data.

Once data is refreshed, click on “Stop Diagnostics”.


Step 5:
When we stop diagnostics, this will add two new tables as a part of Query Diagnostics.
One represents Overall diagnostics and the other represents Detailed diagnostics. Let’s click on detailed diagnostics.

Here, we can see all the steps listed with the time taken for each execution.

From the result, we can easily find out which step is taking the more time.

Also, if we have used any calculated column or condition-based formula, this will also show how much time is taken by that process.

Step 6:
Let’s say if we wish to measure the performance of the specific step, then select the appropriate step from the Query Window, then click Diagnostic Step.


This will add following step(s) which will represent the diagnostics data.

Conclusion:
This is how, we can easily measure the performance of Power Query. Isn’t it amazing?

Happy Reporting!! 

If you have any questions you can reach out our SharePoint Consulting team here.

January 6, 2020

Retrieve User Profile information from Delve using Office 365 Connector in Power Apps

Introduction:
We have come across with specific requirement to fetch User Profile properties from Delve for the specific user, by having a custom People Picker in Power Apps - Canvas App.

Please refer to this article for creating Custom People Picker.

The expected end result is as follows, where the current logged-in user will search for the Employee using email ID and Delve Profile information will be displayed in the App.

So, now let’s start the development process to fulfill the requirement.

A. Create a Template to display User Profile Information:
1. In this App. we are displaying the following Delve properties to the end-user.
  • Name
  • Reports to Manager
  • Department
  • Designation
  • About
  • Previous Projects
  • Employee Photo

2. To display the information, we will use Labels and Image controls in Power Apps.



B. Configure properties to fetch infromation:
3. Once template is created, we need to configure the properties for each control. Please find the below table showing all the information which are mapped for each control.
Property Name
Event for the Selected Property
Formula/ Text/URL
Name
Text
If(IsBlank(SelectedUserEmail),"",Office365Users.UserProfileV2(SelectedUserEmail).displayName)
Reports To
Text
If(IsBlank(SelectedUserEmail),"",Office365Users.ManagerV2(SelectedUserEmail).displayName)
Department
Text
If(IsBlank(SelectedUserEmail),"",Office365Users.UserProfileV2(SelectedUserEmail).department)
Designation
Text
If(IsBlank(SelectedUserEmail),"",Office365Users.UserProfileV2(SelectedUserEmail).jobTitle)
About
Text
If(IsBlank(SelectedUserEmail),"",Office365Users.UserProfileV2(SelectedUserEmail).aboutMe)
Previous Projects
Text
Office365Users.UserProfileV2(SelectedUserEmail).pastProjects.Value
Profile Image
Image
If(IsBlank(SelectedUserEmail),"",Office365Users.UserPhotoV2(SelectedUserEmail))
          **Please refer references section for the other User Properties.

4. Once the properties are configured, let’s check the output. Search for the user and select value from the suggestion box.
And here it is!! The output will be shown in the following screen.

References:
Below is the list of the functions of Office 365 Users.
  • Office365Users.DirectReportsV2
  • Office365Users.ManagerV2
  • Office365Users.MyProfileV2
  • Office365Users.MyTrendingDocuments
  • Office365Users.RelevantPeople
  • Office365Users.SearchUsers
  • Office365Users.TrendingDocuments
  • Office365Users.UserPhotoV2
  • Office365Users.UserPhotoMetadata
  • Office365Users.UserProfileV2

Below is the list of properties which we can get using a user’s email.
  • Office365Users.UserProfileV2(SelectedUserEmail).aboutme
  • Office365Users.UserProfileV2(SelectedUserEmail).accountEnabled
  • Office365Users.UserProfileV2(SelectedUserEmail).birthday
  • Office365Users.UserProfileV2(SelectedUserEmail).bussinessPhones
  • Office365Users.UserProfileV2(SelectedUserEmail).city
  • Office365Users.UserProfileV2(SelectedUserEmail).companyName
  • Office365Users.UserProfileV2(SelectedUserEmail).country
  • Office365Users.UserProfileV2(SelectedUserEmail).department
  • Office365Users.UserProfileV2(SelectedUserEmail).displayName
  • Office365Users.UserProfileV2(SelectedUserEmail).givenName
  • Office365Users.UserProfileV2(SelectedUserEmail).hireDate
  • Office365Users.UserProfileV2(SelectedUserEmail).id
  • Office365Users.UserProfileV2(SelectedUserEmail).interests
  • Office365Users.UserProfileV2(SelectedUserEmail).jobTitle
  • Office365Users.UserProfileV2(SelectedUserEmail).mail
  • Office365Users.UserProfileV2(SelectedUserEmail).mailNickname
  • Office365Users.UserProfileV2(SelectedUserEmail).mobilePhone
  • Office365Users.UserProfileV2(SelectedUserEmail).mySite
  • Office365Users.UserProfileV2(SelectedUserEmail).officeLocation
  • Office365Users.UserProfileV2(SelectedUserEmail).pastProjects
  • Office365Users.UserProfileV2(SelectedUserEmail).postalCode
  • Office365Users.UserProfileV2(SelectedUserEmail).prefferredLanguage
  • Office365Users.UserProfileV2(SelectedUserEmail).responsibilities
  • Office365Users.UserProfileV2(SelectedUserEmail).schools
  • Office365Users.UserProfileV2(SelectedUserEmail).skills
  • Office365Users.UserProfileV2(SelectedUserEmail).state
  • Office365Users.UserProfileV2(SelectedUserEmail).streetAddress
  • Office365Users.UserProfileV2(SelectedUserEmail).surname
  • Office365Users.UserProfileV2(SelectedUserEmail).userPrincipleName
  • Office365Users.UserProfileV2(SelectedUserEmail).userType


If you have any questions you can reach out our SharePoint Consulting team here.

January 3, 2020

How to hide the Filter Pane in published version of Power BI Report?

Problem Statement:
We know that the Filters are very important feature of Power BI desktop. Sometimes, as a developer, we use the functionality of the filter at the visual level, page level, and report level to set the required business logic.

When we publish the Power BI report to the service, at that time, the Filter Pane is clearly visible to end-users. If the filter is not in the locked stage, then end-users can easily change the value of the filters which we have applied to set the business logic. This may spoil the data for the visualization we have configured.

In order to overcome this situation, we can hide the Filter Pane from the published version of the Power BI report. Now, let’s see how we can easily hide the Filter Pane from the Power BI Online/Service.

Solution:
Step 1:
Open the Power BI Report in Power BI Desktop. When we create any Power BI report, the filter pane is opened in the right side of the screen.
Step 2:
To hide the Filter Pane from the published version of the Power BI Report, click on the Hide icon from the filter pane.

Once, we click on the “Hide” icon, the icon will be changed as shown in the following screen.

Step 3:
Publish the Power BI Report, the Filter Pane is now hidden from the Power BI Service.

Conclusion:
This is how, we can easily hide the filter pane from the Power BI Online. Isn’t it amazing??
Stay connected with us for more articles!!

Happy Reporting!!

If you have any questions you can reach out our SharePoint Consulting team here.