MGDC for SharePoint FAQ: How to create custom columns in Power BI

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Introduction

When you use Microsoft Graph Data Connect for SharePoint, the data will commonly be visualized in Power BI.

Once you get the data in Power BI, you will find that creating additional custom or calculated columns can be very helpful. They will help you with your visualizations and enable additional ways to pivot the data.

How to Create a Custom Column

Creating a new custom or calculated column in Power BI is fairly easy. When editing a dashboard, you go to the Data column on the left and click the ellipsis (…) on the top line (name of the query) and select the option to create a new column. See below:

An edit line shows up. Just use the format: NewColumnName = Expression. Here’s an example:

You can have multiple new columns and they show up with an icon that includes the letter “fx”. You can use these columns in any of your reports or dashboards.

Data Analysis Expressions (DAX)

The expression language used by Power BI in this case in called DAX. These expressions are also used by Analysis Services and Power Pivot in Excel data models.

You can find a complete reference at https://learn.microsoft.com/en-us/dax/

As you would expect, it includes all kinds of functions, including:

• date and time (date, year, month, day, hour, minute, second, weekday, dateadd, …)
• financial (fv, pmt, nper, rate, sln, xnpv, yield, …)
• logical (if, and, or, not, bitand, bitor, bitxor)
• math (abs, roundup, power, sqrt, log, ln, rand, pi, sin, cos, tan, …)
• statistical (median, stdev.s, var.s, beta.dist, schisq.dist, norm.dist, …)
• text (left, right, mid, replace, trim, concatenate, upper, lower, …)

These are just a few examples. There are many other functions available.

Examples with Microsoft Graph Data Connect for SharePoint

For the SharePoint specifically, we have several datasets available via Microsoft Graph Data Connect. You can find those at https://aka.ms/SharePointDatasets. For each one, we can identify a few calculated columns that could be useful.

SharePoint Sites

• Converts total bytes in total gigabytes.
  

  TotalGB = Sites[StorageMetrics.TotalSize]/1024/1024/1024​

• Buckets for sites based on number of files, easy to sort.
  

  FileBucket = IF(Sites[StorageMetrics.TotalFileCount]=0, "A:Zero", 
  IF(Sites[StorageMetrics.TotalFileCount]<10, "B:1-9",
  IF(Sites[StorageMetrics.TotalFileCount]<100, "C:10-99", "D:100+")))

• Buckets for sites based on size, easy to sort.
  

  SizeBucket = IF(Sites[StorageMetrics. TotalSize]=0, "A:Zero",
  IF(Sites[StorageMetrics. TotalSize]<1024, "B:0-1KB", 
  IF(Sites[StorageMetrics. TotalSize]<1024*1024, "C:1KB-1GB", "D:1GB+")))

SharePoint Groups

• Types of groups commonly used for configuring permissions
  

  PermissionType = IF(CONTAINSSTRING(Groups[DisplayName], "Visitors"), "Visitors",
  IF(CONTAINSSTRING(Groups[DisplayName], "Members"), "Members",
  IF(CONTAINSSTRING(Groups[DisplayName], "Owners"), "Owners",
  IF(CONTAINSSTRING(Groups[DisplayName], "Limited Access"), "Limited",
  IF(CONTAINSSTRING(Groups[DisplayName], "SharingLinks"), "Links", "Other"))))

SharePoint Permissions

• Role definitions by type: Full Control, Edit, View or Other
  

  RoleType = SWITCH(Permissions[RoleDefinition], 
  "Full Control", "Full Control", "Read", "Read", 
  "Contribute", "Edit", "Edit", "Edit", "View Only", "Read", 
  "Design", "Edit", "Review", "Read", "Other")​

SharePoint Files*

• Checks if a file is a Word, Excel or PowerPoint file.
  

  IsOfficeFile = IF(
  CONTAINSSTRING(";DOC;DOCM;DOCX;DOT;DOTM;DOTX;XLS;XLSB;XLSM;XLSX;XLT;XLTM;XLTX;PPT;PPTM;PPTX;POT;POTM;POTX;",
  UPPER(";" & Files[Extension] & ";")), "Yes", "No")

• Top level folder in the file hierarchy
  

  TopDir = LEFT(Files[DirName], FIND("/", Files[DirName]&"/") - 1)​

OneDrive Sync Health*

• Simple Yes/No column based on whether there are errors.
  

  hasErrors = IF(SyncHealth[TotalErrorCount] > 0, "Yes", "No")​

• Converts last sync data from a timestamp to a simple date.
  

  LastSyncDate = DATE(YEAR(SyncHealth[LastSyncedTimestampUTC]),
  MONTH(SyncHealth[LastSyncedTimestampUTC]), 
  DAY(SyncHealth[LastSyncedTimestampUTC]))​

• Translates Update Ring from a number to the name,
  

  UpdateRingName = SWITCH(SyncHealth[SyncAppUpdateRing], 
  4, "Insider", 5, "Production", 0, "Deferred", "Other")​

Notes:

• Datasets marked with * are not yet publicly available. Some of them are available as a private preview to specific customers.
• Each formula should be entered as a single long line. I broke it down into several lines here for better readability.

Conclusion

As usual, these examples are shared to spark ideas and help you create your own custom columns. I hope this enables you to author better Power BI dashboards and reports. 

Do you have any ideas for additional calculated columns for any of the SharePoint datasets? Share with us in the comments...

For more information about Microsoft Graph Data Connect for SharePoint, check the overview post at https://aka.ms/SharePointData.