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.
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.
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:
These are just a few examples. There are many other functions available.
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.
TotalGB = Sites[StorageMetrics.TotalSize]/1024/1024/1024
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+")))
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+")))
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"))))
RoleType = SWITCH(Permissions[RoleDefinition],
"Full Control", "Full Control", "Read", "Read",
"Contribute", "Edit", "Edit", "Edit", "View Only", "Read",
"Design", "Edit", "Review", "Read", "Other")
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")
TopDir = LEFT(Files[DirName], FIND("/", Files[DirName]&"/") - 1)
hasErrors = IF(SyncHealth[TotalErrorCount] > 0, "Yes", "No")
LastSyncDate = DATE(YEAR(SyncHealth[LastSyncedTimestampUTC]),
MONTH(SyncHealth[LastSyncedTimestampUTC]),
DAY(SyncHealth[LastSyncedTimestampUTC]))
UpdateRingName = SWITCH(SyncHealth[SyncAppUpdateRing],
4, "Insider", 5, "Production", 0, "Deferred", "Other")
Notes:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.