June 17, 2019

Combine data from multiple SharePoint Lists using Power Query in MS Excel - Part 1

Have you ever received a requirement to get data from SharePoint list and you wanted to modify it, but, you have trouble with the same?

Well, Microsoft Excel has been evolving on a regular basis, making it a powerful tool. But are we aware of the powerful things, that we don't use on a daily basis? Here, is one such function i.e. Power Query.

What is Power Query?
Power Query is an add-in for business intelligence available in Microsoft Excel and now in Power BI. Similar to SQL query, it gives you the functionality to modify or create new model of the data by combining various data sources like relational databases, Excel, text and XML files, OData feeds, web pages, SharePoint, Hadoop file (HDFS), Exchange, SAP HANA, etc. and utilize it for further processes. 


Example:
Here, in this example, we would be getting the data of 2 SharePoint lists, and combining those, to make a new model for our use. 


Please Note: This blog provides information on the basics like using the Power Query, creating functions, and using those functions to model data for further use.

In this example, we have 2 SharePoint Lists - Subject Name and Students. 
  • Subject Name list is the master list of the subjects. 
  • Students list contains the data of students of different grades and Subject Name they excel at. 
Requirement:
We need to get list of students those who are Primary school i.e. Grades <=7, and High School i.e. Grades > 7 in Excel.



Implementation : Connecting SharePoint List with Excel and transforming data.

So, we begin with connecting SharePoint online data with Excel:
  • In the Data Tab, from the ribbon > Get Data. In this you would find various data connectors, and we would be going for SharePoint Online List. So, under the "Get Data menu > From Online Services > From SharePoint Online List.
  • Now, in the open SharePoint List dialog box, we would enter the Site Collection/Subsite URL of List1.
  • This would ask us to authenticate the details, and ask us for the credentials.
  • On Clicking Sign In, this would open Office 365 login page pop-up dialog box. Enter the account details. On authentication, it would show that you have signed in and Click on Connect. 
  • On Clicking Sign In button, this would open Office 365 login page pop-up dialog box. Enter the account details.
  • On authentication, it would show that you have signed in and Click on Connect. This would open up the total list and libraries in the site. 
  • Here, select the List1 from which the data is to be fetched. In our case, we would be using Subject Names list. On selection, on the right, you would see the preview of the data in a table. 

Please note, that while fetching the data, it brings all the data in the list, including the hidden and back-end columns, not generally used and is recommended not to be used.
  • Now, there are 2 options, Load and Edit.
    1. Load: This would fetch the information as it is. Then, the changes would be made in the Excel file, but every-time, the data would fetch all the information. This would being unwanted data, and specially, when the data volume is very high, this is not recommended.
    2. Edit: The recommended approach, if the data is too large, to have a cleanup performed. This would open up our table, where we can make changes in the model  
  • So, we would be click on Edit to edit the fields, to change the model and get limited data only. This would open up Power Query Editor window. 
  • We will remove the unwanted columns, by selecting the columns and then right-clicking the column to remove the columns. 
  • We have removed the unwanted columns and our List 1 is ready. Now, we have just the Subject Name and Description. This would look like something as below.
  • Now, we would be adding another list here, in the same Power Query Editor window. We can add our 2nd list, by clicking New Source > Online Services < SharePoint Online List. Alternatively, we can right-click in the Queries section and select New Query > Online Services > SharePoint Online List. This would open the same dialog box to enter the SharePoint Site URL.
  • We will add Students which contains the Student Names, their grades and the subject name (lookup of the Subject Name). We will clean it by removing unwanted columns, and we would be having just Student Name, Grades and Subject Name. Now, as this is a lookup, we would be seeing "Record". So, we would be expanding it and selecting the Title column i.e. - Subject Name. To expand, we will click the expand button besides the column Subject Name and select Title column.
  • So, in this part, we have added the data source list, and modified the data as per our requirement.
In the next part, we would creating function in Power Query Editor, to get the students per the grade, and and use those for different purpose. 

Use SharePoint data in Microsoft Excel using Power Query - Part 2

#PowerQuery #MSExcel #Excel #Query #SharePoint #Office365 #SharePointOnline #SharePointList #PowerBI #MicrosoftSharePoint


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

June 7, 2019

Column formatting in SharePoint Modern List View

Requirement:
While working with one of SharePoint Modern UI project, there was requirement to display Reminder Date with red background color in  default List View, if it is due within next 7 days from today.

Challenge: 
JS link is the one way to meet the requirement. But in OOTB Modern List view, there isn't any option to reference JS link in Miscellaneous section which is available in Classic view.




Solution:
Using Column Formatting option, we've achieved the desired results. You can find this option while editing the column as shown in below screenshot.


Here, we need to write JSON code to format the field value. Here is code snippet to display "Reminder Date" field with red background and text color in white.

{
  "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
  "elmType": "div",
  "debugMode": true,
  "txtContent": "@currentField",
  "style": {
       "color":  "=if([$Reminder_x0020_Date] <= @now+604800000, if([$Reminder_x0020_Date]                            >= @now,'#ffffff',''), '')",
"background-color":"=if([$Reminder_x0020_Date]<= @now+604800000,if([$Reminder_x0020_Date] >= @now,'#ff0000',''), '')" }
}

Note: 604800000 is in millisecond, equals to 7 days.

Output: Date column will be rendered in default list view as per below screenshot.


Limitation:
  • It will not work with SharePoint Online Classic View.
  • If formatting of the column is based on value of another column from the list, than that column must be available in List View.
If you have any questions you can reach out our SharePoint Consulting team here.

February 7, 2019

Multithreading in PowerShell & Exchange Online (Office 365)


Challenge:
The details of a few (around 300) users' mailbox in Exchange Online were to be fetched, and the approach is via PowerShell. Now, the total users were over 3800, and to filter from those, consumed a lot of time. As a result, going the conventional way of getting those one-by-one took us 39 seconds per user which was around 3.5 hours for the whole script execution.  

Resolution:
We had to find another way to get this done. So, we chose the approach of getting multiple records simultaneously, using background processes. 

And, multi-threading came to the rescue!!

We created the connection to Exchange Online via PowerShell:
$Credentials = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri  https://outlook.office365.com/powershell-liveid/ -Credential $Credentials -Authentication  Basic -AllowRedirection

Import-PSSession $Session 

Then, we read the user display names from the file, and stored them in an array. 
$accounts = Import-CSV C:\Temp\File.csv

Now, instead of specifying the number of jobs, we would want the jobs to be automatically created.

So, we would be specifying, the batch size of each job. In our case, we chose 50 users per batch.

$accountsperBatch = 50

Then, we iterated the loop for all the accounts to be split into batches as jobs, and then started the job using Start-Job for creating the job. 

This Start-Job  needs:

1. ScriptBlock in order to job to know, what it has to do.
2. ArgumentList in order to job to know, the items using which the job is to be performed.    

On specifying this, PowerShell would begin the creation of jobs, and as the job is created, the code within the ScriptBlock is executed.  
$accountsperBatch = 50
$i = 0
$j = $accountsperBatch - 1
$batch = 1
$csvContents = @() 
  
while ($i -lt $accounts.Count)
{
 $accountBatch = $accounts[$i..$j]
 
 $jobName = "Batch$batch"
 $fileName = "c:\Temp\Details$jobName"
 Start-Job  -Name $jobName `
       -ScriptBlock {
         param([string[]] $accounts)
  foreach($account in $accounts)
  {
   $mailBoxDetail = Get-Mailbox  -Identity  $account | Select  DisplayName, UserPrincipalName 
   $row =  New-Object System.Object
   $row | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value  $mailBoxDetail.DisplayName
   $row | Add-Member -MemberType NoteProperty -Name "UserPrincipalName" -Value  $mailBoxDetail.UserPrincipalName

   $csvContents += $row  
  }
                $csvContents | Export-CSV $fileName
             }`
      -ArgumentList (,$accountBatch)
     
 $batch += 1
 $i = $j + 1
 $j += $accountsperBatch
 
 if($i -gt $accounts.Count) {$i = $accounts.Count}
 if($j -gt $accounts.Count) {$j = $accounts.Count}
}

Then, as per the best practices, we would remove the jobs completed by using Remove-Job  and the PS Session using Remove-PSSession $Session. 

This script helped us to generate the details of the user accounts simultaneously and the results improved drastically and the results were available under 1.5 hours.

Note: If we open too many connections with Office 365, it would suspend the connections, as the security mechanism at Office 365 would consider this as a DDoS attack, thus, giving us an error like remote host not found or the connection was closed. 

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