April 8, 2020

Get multiple List Items and send merged response to Power Apps through MS Flow

Introduction:
In this article, we will walkthrough on how to get items from multiple SharePoint Lists having the same list structure schema, combine the list items and send merged list items response to the Power Apps.

Real-Life Use-case and Scenario:
We have come across a scenario wherein we have one master list having the name of all child lists from where we need to fetch the data. Let’s say for an example –

We have a master list named “ABC MasterList”. The column called “ListNames” from the list has the information of the all child lists. Please see the below screen.

Now, we need to loop through on column – “ListNames” and find out the items from those lists. Let’s say in “ListNames” column if there are 2 lists name, then this will loop through those two lists, get all items from those lists and send the response back to the Power Apps. So, now let’s get started!!

Step 1: Create Flow with trigger action of Power Apps.  In the end, the flow will look like the below screen.

We need to initialize one variable in which we collect all the list items and send this variable to Power Apps response. We need to create one string variable “Items” as shown in the below image.

Step 2: Add an action called – “Get items from SharePoint list” to get all list names from the master list.

Step 3: Make an Http request to fetch the items from each list fetched from Step 2. Below is the overall looping step.

Now, let’s see each component.

Step 3.1: Click on the “Add an action” button.



Search “select”  in the search box and select the “Select” from Actions as shown in the below image.





Select the column which you want to send to the response.



Step 3.2: Click on the “Add an action” as shown in step 3.1. Select the “Join” from Actions as shown in the below image.

Join the output of “Select” with “\n” for separation.

Step 3.3: Append the “Output” to the string “Items”.

Step 4: Now, let’s send the response back to the Power Apps. Add Response Variable with the value assigned to it. And then send the above string variable to Power Apps response.

Now, let’s run and test the flow. Below is the test result.

Also, this is the value for the final variable which is returning to the Power Apps.

Conclusion:
This is how we can fetch the data from the multiple lists having the same schema and send the final response back to the Power Apps.

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

April 6, 2020

Create Dual Y-Axis Line Chart in Power BI

Overview:
In this blog, we will learn how we can create a Native Line chart with dual Y-Axis in Power BI. Sometimes, there are situations where we need to represent two Y-axis in the line chart. Earlier Dual Y-Axis feature was not available in Power BI but it got released with March 2020 - Power BI Updates.

Example:
We want to represent Actual and Budget Sales with the # of units sold in the line chart. We need to represent this with two Y-Axis.
  • One which represents Sales Data
  • The second which represents # of Units
Now, let’s get started!

Step 1: Add a Line chart from the visualization pane.

Step 2: If you carefully observe, we can see the Y2 Axis in the format section.

Drag Sales fields to Values section and Units to Y2 Axis.

Step 3: The Dual Y-Axis line chart will look like this.

Also, we can format the Y2 Axis using the following option.

Conclusion:
This is how we can easily create Dual Y-Axis line chart in Power BI. Happy Reporting!

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

March 30, 2020

Manage more than 100 API calls with Batch Request in SharePoint Framework (SPFx) using React

Requirement:
We have IDs of 1000 list items and want to fetch list items for only those particular IDs using Rest API with OR condition in the filter.

Problem Statement:
  1. If we append all IDs in the filter parameter with OR condition, it will give REST API length limitation (255 characters).
  2. If we use API with OR condition for all IDs and use this API in a batch request, batch API will return a blank array.
  3. If we create API for each ID and create an array of APIs & use this array in a batch request, it will work for maximum up to 100 APIs only. As we have 1000 list items to be retrieved, this will also not work due to the 100 APIs limit with a single batch request.

Solution:
We can create API for 100 Item IDs with OR condition in the filter API and create a bunch of 100 APIs in an array and then use this array in a batch request.

You can find batch utility code here on GitHub. Here is the code snippet for this solution:
 //Here we have imported batchutility.ts  
 import { BatchUtils } from "../../BatchUtils";  
 public componentDidMount() {  
   var listItemIds = [1, 2, 3, 4, 5, 6, ..............................., 1000];  
   var url = this.props.SPUrl + "/_api/web/lists/getbytitle('" + this.props.projectPhaseListName + "')/items?$filter=";  
   this.getDashboardBatchData(listItemIds, 0, listItemIds.length, url);  
 }  
 //Below function will create bunch of filters with max of 100 id in filter with or condition  
 public getDashboardBatchData(listItemIds, Index, totalCount, url) {  
   var loopLen = Index + 100;  
   if (Index <= totalCount) {  
     var filterString = "";  
     var tempApi = '';  
     var callNext = true;  
     if (totalCount > Index && totalCount < loopLen) {  
       loopLen = totalCount;  
       callNext = false;  
     }  
     for (var i = Index; i < loopLen; i++) {  
       if (filterString == '') {  
         filterString = "ID eq " + listItemIds[i];  
       }  
       else {  
         filterString += " or ID eq " + listItemIds[i];  
       }  
     }  
     tempApi = url + filterString;  
     dashboardBatchArray.push(tempApi);  
     if (callNext) {  
       this.getDashboardBatchData(listItemIds, loopLen, totalCount, url);  
     }  
     else {  
       //dashboardBatchArray will have all APIs with max of 100 filters for ID  
       this.processBatch(dashboardBatchArray);  
     }  
   }  
   else {  
     //dashboardBatchArray will have all APIs with max of 100 filters for ID  
     this.processBatch(dashboardBatchArray);  
   }  
 }  
 //Below funcion will create bunch of 100 APIs and will be used in batch request  
 public processBatch(dashboardBatchArray){  
   var index = 0;  
   var arrayLength = dashboardBatchArray.length;  
   var tempArray = [];  
   var chunk_size = 100;  
   //Below code will create array of 100 APIs in single bunch  
   for (index = 0; index < arrayLength; index += chunk_size) {  
     var myChunk = dashboardBatchArray.slice(index, index + chunk_size);  
     tempArray.push(myChunk);  
   }  
   //Below code will execute all apis of tempArray  
   let listItemsArray = [];  
   for (var i = 0; i < tempArray.length; i++) {  
     await BatchUtils.GetBatchAll({ rootUrl: this.props.SPUrl, FormDigestValue: '', batchUrls: tempArray[i] }).then((batchResult) => {  
       if (batchResult.length > 0) {  
         for (var i = 0; i < batchResult.length; i++) {  
           for (var j = 0; j < batchResult[i].d.results.length; j++) {  
             listItemsArray.push(batchResult[i].d.results[j]);  
           }  
         }  
       }  
     }  
   }  
   //You will see all 1000 items in listItemsArray variable  
   console.log(listItemsArray);  
 }  

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

March 26, 2020

How to change Date and Time Format in Microsoft Flow or Power Automate?

Introduction:
In this blog, we will learn, how we can change the date and time format in Microsoft Power Automate. Sometimes, there are situations in which we need to change the format of the date and time value.

Let’s say for an example if our date time value is 2020-01-30T07:51:09Z and we wish to convert this value to “01/30/2020 07:51:06 AM” format, then we need to use some expression in MS Flow or Power Automate.

Real-Life Use Case:
In this article, we will format the date and time value to “MM/dd/yyyy hh:mm:ss AM/PM” format for SharePoint list item. We already have a SharePoint list with OOTB “Created” column. We will change the format of Date and Time in Power Automate. So, now let’s get started!

Step 1:
We have the following list structure. and would need to convert "Created" column to “MM/dd/yyyy hh:mm:ss AM/PM” format, and store this converted value in a variable.

Step 2:
Add a Trigger: When an item is created to the SharePoint list. Add Site Address and List Name.

Step 3:
Initialize variable, to store the value of the converted date and time. Add an action: Initialize variable.

Step 4:
Now, let’s set the variable and change the date and time format using below formula:

formatDateTime(triggerBody()?['Created'],'MM/dd/yyyy hh:mm:ss tt')

Here, we need to pass, the date time value as a first parameter and in the second parameter, we need to pass the format for the specific date and time value. 

Save the flow. We can check the formatted date and time value from this variable.
In the end, MS flow will look like in the following screenshot.

Now, let’s run the flow and check the result.

Test:
Let’s create one item in the SharePoint list to trigger a flow.

Here, our date and time format for the “Created” date is changed and stored in the variable successfully.

Conclusion:
This is how, we can easily change the format for the date and time column in Microsoft Power Automate. Isn’t it amazing?

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

March 2, 2020

Office 365/SharePoint Online - PowerShell Script to get Unique Visitors & Total Views count for Site Page

Problem Statement -
We have a client with a large SharePoint Tenant, want to know the Total Views count along with Unique Visitors count for each Post/Page, they are adding every day for their employees.

Analysis -
Currently, Microsoft is working on Analytics API that is supposed to give you the details regarding total view count and unique view count for specific item. Even though it is released but not working for a single item.

The alternative way is to use the SharePoint Search API. This will use Microsoft Classic Search results and provide you count based on that.

Resolution -
We have decided to go with the SharePoint Search API approach. We have found an article with the same idea. The only concern was, using Search API directly can return max 500 rows as a result at a time. So, we need to generalize that script in such a way that it can be executed for all items in the list/library.

So, here is generalized the Power-Shell script:

Step 1 - Load required dependencies/assemblies:
 # Paths to SDK. Please verify location on your computer.  
 #Add-PSSnapin Microsoft.SharePoint.PowerShell  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll")  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Search.dll")  

Step 2 - Create a Function - to get all list items and using search query to retrieve ViewsLifeTime & ViewsLifeTimeUniqueUsers for each item:
 function Get-SPOListView  
 {  
   param(  
   [Parameter(Mandatory=$true,Position=1)]  
   [string]$Username,  
   [Parameter(Mandatory=$true,Position=2)]  
   $AdminPassword,  
   [Parameter(Mandatory=$true,Position=3)]  
   [string]$Url,  
   [Parameter(Mandatory=$true,Position=4)]  
   [string]$ListTitle  
   [Parameter(Mandatory=$true,Position=5)]  
   [string]$TenantUrl  
   )  
  #Get the SharePoint List/Library.
   $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)  
   $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $AdminPassword)  
   $ll=$ctx.Web.Lists.GetByTitle($ListTitle)  
   $ctx.load($ll)  
   $ctx.ExecuteQuery()  
  #Get all items from the List/Library.
   $qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()  
   $items = $ll.GetItems($qry)   
   $ctx.Load($items)  
   $ctx.ExecuteQuery()  
   foreach($listItem in $items)  
   {  
     Write-Host "ID - " $listItem["ID"] "Title - " $listItem["Title"] "EncodedAbsUrl - " $listItem["FileRef"]     
     $fileurl = $TenantUrl+ $listItem["FileRef"]  
     #Using Search API - Create the instance of KeywordQuery and set the properties.
     $keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($ctx)   
     #Sample Query - To get the result of last year.
     $queryText="Path:" + $fileurl  
     $keywordQuery.QueryText = $queryText  
     $keywordQuery.TrimDuplicates=$false  
     $keywordQuery.SelectProperties.Add("ViewsLifeTime")  
     $keywordQuery.SelectProperties.Add("ViewsLifeTimeUniqueUsers")  
     $keywordQuery.SortList.Add("ViewsLifeTime","Asc")   
     #Search API - Create the instance of SearchExecutor and get the result.
     $searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($ctx)  
     $results = $searchExecutor.ExecuteQuery($keywordQuery)  
     $ctx.ExecuteQuery()  
     #Result Count  
     Write-Host $results.Value[0].ResultRows.Count  
     #CSV file location, to store the result  
     $exportlocation = "C:\Pages_ViewsCount - Copy.csv"  
     foreach($result in $results.Value[0].ResultRows)  
     {  
       $outputline='"'+$result["Title"]+'"'+","+'"'+$result["Path"]+'"'+","+$result["ViewsLifeTime"]+","+$result["ViewsLifeTimeUniqueUsers"]   
       Add-Content $exportlocation $outputline   
     }   
     #}  
   }  
 }  

Step 3 - Call the above function:
 # Insert the credentials along with Admin & Tenant URLs and Call above Function.  
 #Enter Username here  
 $Username="username@sharepoint.com"  
 #Enter Password Here  
 $AdminPassword=Read-Host -Prompt "Password" -AsSecureString  
 #URL of the site collection  
 $AdminUrl= "Site collection URL"  
 $ListTitle= "Site Pages"  
 $TenantUrl= "Your tenant URL E.g. https://YourCompany.sharepoint.com"  
 Get-SPOListView -Username $Username -AdminPassword $AdminPassword -Url $AdminUrl -ListTitle $ListTitle -TenantUrl $TenantUrl  

It will export all items of the list with Total views count - ViewsLifeTime & Unique views count - ViewsLifeTimeUniqueUsers in excel sheet as shown in below image:


Conclusion - This way, we can retrieve the item level unique visitors count & total view count.

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

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.