Posted on

New book! MS-101 Exam Study Guide Announcement

Earlier this year, in January 2019, I wrote an MS-100 and MS-101 exams study guide blog post linking to resources to help people prep for both exams. MS-101 in particular seemed to have less available for it, and I jumped at the opportunity to change that. I’m pleased today to be able to add my own, new exam prep book to the listing.

My exam prep guide for MS-101: Microsoft 365 Mobility & Security covers every single objective in the exam description and offers mock questions to help check and solidify learning along the way. Passing MS-101 is a step toward becoming an M365 Certified Enterprise Administrator Expert.

You can currently pre-order my guide on Amazon or directly through my publisher, Packt.

Pre-Order Now

Test for basic competence in planning, deploying, and managing Microsoft 365 services with MS-101: Microsoft 365 Mobility and Security exam guide

Key Features

  • Learn everything from mobile device management and compliance to data governance and auditing
  • Get up and running with managing device compliance, Azure Advanced Threat Protection (ATP), Azure Information Protection (AIP), and more
  • A practical guide filled with exam objective focused practice exercises and mock tests

Book Description

Written in a clear, succinct way with self-assessment questions, exam tips and mock exams with detailed answer explanations, this book covers common tasks in mobile device management (MDM) and device compliance, security reporting and alerts, threat detection and management, data loss prevention (DLP) and data governance, auditing and eDiscovery, and Azure information protection (AIP).

You’ll learn how to properly plan for, deploy, and manage Microsoft 365 services such as MDM and DLP. You’ll discover best practices in properly configuring settings across your tenant to ensure compliance and security.

By the end of this book, you’ll have covered everything you need to pass the MS-101 exam and have a handy, on-the-job desktop reference guide.

What you will learn

  • Implement modern device services
  • Implement Microsoft 365 security and threat management
  • Manage Microsoft 365 governance and compliance
  • Plan for, deploy, and then manage Microsoft 365 services such as MDM and DLP
  • Learn the best practices in properly configuring settings
  • Pass the exam using the self-assessment questions, exam tips and mock exams in the book

Who This Book Is For

This book is ideal for professionals seeking the M365 Enterprise Administrator Expert certification. Audience will have already taken or plan to take MS-100 as well, and have experience or interest in administering the entirety of a Microsoft 365 tenant and its services.

Posted on

Connect a Google Sheet spreadsheet to Power BI as a data source

I recently connected Power BI directly to a spreadsheet in Google’s Excel alternative, Google Sheets. It’s important to connect to data without moving it when we can. The less we have to export/download, save, rename, move, massage, etc. the better.

Here’s how we’ll connect to a Google Sheet:

  1. Get “no sign-in required”/anonymous share URL from Google Docs
  2. Adjust URL for Power BI
  3. Connect to “Web” data source in Power BI, using adjusted URL

Get anonymous share link from Google Docs

To get started, open your Google doc/sheet and click “Share.” If you don’t see “anyone (no sign-in required)” or something similar, click “More.” Then select “On – Anyone with the Link.” Copy the resulting URL.

Adjust URL for Power BI

  1. Paste the URL into Notepad, or a similar, simple text editor.
  2. Remove /edit?usp=sharing from the URL and add /export?format=xlsx&id= in its place
  3. Copy the ID between /d/ and /export… (see red box below)
  4. Paste the ID at the end of the URL (see green box below)
Click to enlarge

Connect to Google Sheet in Power BI

1. Get Data > Web

2. Paste your adjusted URL and click OK.

As long as you used the correct Google Sheet share link (anyone with the link can view), you’ll see your data ready to load or transform as usual in the Navigator screen. If you get an error, double-check the share link type.

Acknowledgment

Thanks to GGetty at this thread for pointing me in the right direction. Note one of the comments from 2017 warning you may need to be owner of a file (having edit rights may not be sufficient) doesn’t seem to be true anymore. I successfully connected to a file I only had edit rights to.

Posted on

How to create a PTO/vacation request form and approval process in SharePoint

I was recently asked to develop a PTO request form that accomplished the following:

  • Allowed users to submit PTO, Remote work, Training, and Other requests
  • Auto-lookup of the employee’s manager
  • Send confirmation to submitter
  • Notify manager and seek approval/rejection with comments
  • If approved, send notification with an iCal link users can save to their calendar, and add the PTO to our shared departmental calendar (minus any comments)

Note: This particular build was done on SharePoint Server 2016 and uses a SharePoint Designer workflow.

Create the PTO request form

Thanks to Gregory Zelfond for sharing the idea of using a Tasks web part for this as opposed to a list. Much of this first section is inspired by his post here, with some adjustments for my purposes and preferences.

  1. Add a tasks app and name it PTO Requests

  2. Go to List settings for the new tasks app
  3. Configure Versioning settings as follows

  4. Back under List settings, configure the following Advanced settings as seen here

      

  5. Under Views, keep All Tasks, My Tasks, Approve/reject Items, and Calendar. Delete the rest. Rename All Tasks and My Tasks to All Requests and My Requests.

  6. Back under List Settings, under Columns, create the columns shown in green here, and rename those in red. Duration should be the last column you create (number format), and its formula follows the screenshot. Category needs to be the types of “Out of Office” a person could request (like PTO, Remote, Training, Other).
    Click to enlarge

     

    =IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

    I like the Duration column because it will allow us to show how many weekdays (not including weekends) the PTO Request includes. So those multi-week or broken-week requests show an accurate number for management purposes.

  7. Back under List Settings, select “Task” from under Content Types. Configure each column (hide, make optional or require) as follows:
    Click to enlarge

     

  8. Your new form should resemble this now:
         
    Click to enlarge

     

  9. You might want to spend some time making your views a nice dashboard as well. Here’s what I did for “All Requests.” Notice I grouped by a different calculated column which determines whether the PTO is Upcoming, Current, or Past and then sub-grouped by category.

    Click to enlarge

Create the workflow for approval and calendar addition

In SharePoint Designer, add a new 2010 platform List Workflow for the PTORequests list.

  1. Go to Workflow Settings
  2. Check all “Start Options”
  3. Click “Edit workflow”

Here’s a preview of what we’re going to build:

Click to enlarge

Because this workflow has many steps, I’m only going to show some high-level tips for completing it. Build your workflow to suit your needs. If you have specific questions on how I built mine, please comment.

The approval request email

For the “then Email Current Item: Approver” step, here’s how I did the email:

The link for approving and rejecting is as follows, and you’ll need to replace the red text with a lookup to the current item’s ID. The easiest way to get this might be to do a test submission and copy the link to the approval page where the manager will choose Approved, Rejected, or Pending. You can find this page by viewing a submission, then clicking “Approve/Reject.”

Then just be sure to swap the specific ID with the lookup field for ID.

https://YOURSITE/sites/it/_layouts/15/approve.aspx?List=%7BLISTGUID%7D&ID=[%Current Item:ID%]&Source=%2Fsites%2Fit%2FLists%2FPTO%2520Requests

This will reduce the number of clicks your approvers/managers have to do significantly. Their process will be:

  1. Click big link in email
  2. Select decision and enter comments
  3. Save/Submit

If denied email

No bells and whistles here. You might even include a link to the dash suggesting they submit another request if they wish.

If approved, create calendar item

This is fairly straightforward. If approved, we create an item in a different list. I even edited the “Calendar” view of my PTO requests by adding an “app part” for the separate calendar instead of using the built-in calendar view the task list provides. I then just minimized and hid the chrome of the “Calendar” web part that was already on the view. This allowed me to keep some overlays and other processes related to the second calendar, but see if in context of PTO Requests.

Also, to protect the privacy of those submitting “Other (please explain)” requests (likely FMLA or something private) I have the workflow changing “Other” to just “Out of Office” for the purposes of adding to our shared calendar.

If approved email

Since I created a calendar item if approved, I’m including an iCal link in the approval email for users to open the .ics file and save it to their calendars. Here’s a post on how to structure iCal links. Just instead of creating a calculated column, construct the URL in SharePoint Designer:

Click to enlarge

Improve the look with script (optional)

The following script improves the look of both your views and your forms. It will:

  • Get rid of  “See also” items when viewing requests
  • Change “New task” to “New request” in your views
  • Make sure all fields are visible, and not hidden behind “Show More”

Just copy and save the following script as a separate .js file in your Site Assets and follow the instructions beneath the script to add it to your views and forms. 

Views

  1. Go to a view
  2. Settings –> Edit Page
  3. Insert/Add web part –> Media and Content –> Content Editor Web Part
  4. Add link to script saved on site
  5. Save and repeat for each view

Forms

Go to your list –> List –> Form web parts

Click to enlarge
  1. Select a web part
  2. Settings –> Edit Page
  3. Insert/Add web part –> Media and Content –> Content Editor Web Part
  4. Add link to script saved on site
  5. Save and repeat for each form

Good luck!

Posted on

Possible solution: “Sorry, something went wrong. The file is locked for exclusive use by…”

I recently ran into the following error when someone tried to edit a shared Excel file from OneDrive (we’re running SharePoint Server 2016).

“Sorry, something went wrong. The file {address} is locked for exclusive use by {name}.”

Troubleshooting

The user in question, who had it “locked for use,” had the file open in Excel (client, not online) when their computer unexpectedly shut down. This locked others from editing the file. The first few things you should try (stop if one of the steps fixes the issue):

  1. If the user still has it open, have them save and close out. If it really was checked out, check it in with their account.
  2. Make sure the user who has it locked has closed all office apps (Task Manager is a good way to see if anything is running in the background)
  3. Have that user restart their machine used to edit the file in the first place for good measure
  4. You can try waiting a day to see if the lock lifts overnight or after a few hours.

What worked for me

Now that you’ve confirmed the individual who had the file locked can’t really have it open or locked, you can try the solution that worked for me. At the bottom of this post, I’ve listed more ideas I tried that might work for you.

  1. Open the user’s OneDrive in SharePoint Designer (the address may resemble https://mysites.COMPANY.org/personal/USERNAME)
  2. Navigate via “All Files” via the left nav to the file in question. It probably has a padlock icon on it.
  3. Right-click the file name and select “Edit File in Advanced Mode”
  4. Save the file in SharePoint Designer.

I can’t explain why this worked, I know it’s completely illogical, but it worked.

Here’s a thread on SharePoint’s user voice regarding this error.

What might work for you

Here are some things you can try that were not successful for me (but might work for you):

  • I tried copying the file and deleting the original but could not delete it (in browser, PowerShell, or SharePoint Designer)
  • I tried this PowerShell solution and it told me who had it “checked out,” and when it would expire which was helpful, but it was unable to release the lock
    • P.S. I waited for the expiration, but it renewed itself
  • I tried checking it in via browser and SharePoint Designer (because it appeared checked out) but got a message saying it wasn’t
    Click to enlarge
  • From the owner’s OneDrive (where the file was stored/created) I disabled check-out requirement via Site Contents –> Library Settings –> Versioning settings
    Click to enlarge
Posted on

GCC solution: “Your org doesn’t allow Microsoft Flow. You’ll need to use your personal email to sign up and get access.”

I’m currently on an adventure into my first GCC (government) tenant. When I went to https://flow.microsoft.com in this new GCC tenant, I got the following error:

“That didn’t work. Your org doesn’t allow Microsoft Flow. You’ll need to use your personal email to sign up and get access.”

I was in the habit of using that URL for quick access. Turns out the GCC tenant uses a different Flow. If you use the app launcher in your tenant to get there, you’ll notice the correct URL to memorize is https://gov.flow.microsoft.us (or your locality).

If that doesn’t work for you, and you’ve tried to get to Flow through the App Launcher (waffle icon in the upper left of Office online or SharePoint), it could very well be that your organization doesn’t allow Flow.

One major difference I’ve noticed so far is that Flow for government O365 tenants only has around ~85 connectors currently where as Flow for commercial O365 tenants has around ~275 connectors.

The following are the available connectors currently in GCC tenants:

Click to enlarge
Posted on

Add or subtract hours from date/time values in Power BI using M formula in Power Query Editor

Previously, I shared how to transform date/time values in Power BI using DAX. But I’m working on building more using M formulas as those are less resource-intensive and improve report performance.

The following was my solution for correcting a time zone issue in which the original data’s date/time column was 5 hours ahead.

=[#"Date/Time"] - #duration(0, 5, 0, 0)

That example subtracts 5 hours from a column called Date/Time. You can change the column name to your own, of course. Also update the numeric values in the #duration part to add or subtract days, hours, minutes, and seconds, respectively.

#duration(days, hours, minutes, seconds)

And just change the – to + to add instead of subtract.

Examples

Add seven days (a week)

=[#"Date/Time"] + #duration(7, 0, 0, 0)

Correct a time zone – Add hours

=[#"Date/Time"] + #duration(0, 5, 0, 0)

Correct a time zone – Subtract hours and minutes

=[#"Date/Time"] - #duration(0, 2, 30, 0)

Learn more about M formulas and functions

Check out “Add a custom column in Power BI Desktop” from Microsoft Docs to get started with creating custom columns using M formulas instead of DAX.

This Power Query M function reference guide is super helpful for learning more about available functions to use in your M formulas in Power Query Editor.

Posted on

Show/hide fields conditionally in PowerApps forms based on dropdown selection

I’m working on digitizing a form to improve the user experience and our data collection and availability efforts. I decided to do this one in PowerApps as we begin to pivot organizationally toward the modern experience.

During building this particular form, I needed to hide fields unless the user indicated “Yes” to corresponding dropdown fields. Here’s the end-result:

Click to enlarge

To get this to work we have to do three steps:

  1. Initialize variables for visibility
  2. Set the conditional fields’ visibility to the new variables
  3. Set the dropdown to toggle the related variable

Initialize variables for visibility

First we need to tell our app that we’ll be using true/false variables to indicate the visibility of our conditional fields. We do this from the OnVisible property (2) of the Form Screen (1).

Click to enlarge

The formula itself should be as follows (3). Use a semi-colon between each UpdateContext function to add additional variables. I’m using aVisible, bVisible, etc.

UpdateContext({aVisible: false});UpdateContext({bVisible: false});UpdateContext({cVisible: false})

So in your documentation you might note something like:

Variable Default state Changed by Hides/Shows
aVisible false (hidden) BAARequired
(dropdown)		 BAAEffectiveDate
bVisible false (hidden) ConflictsOfInterest
(dropdown)		 Conflicts…Completed
cVisible false (hidden) DataSecurityRisk
(dropdown)		 DataSec…Completed

Note: You can use the same variable more than once to show/hide multiple fields based on one dropdown.

Set the conditional fields’ visibility to the new variables

Now we need to assign these variables to the DataCards for each field we wish to hide.

From the Tree View panel, select the DataCard (not the DataCardValue within/beneath it) for the field you wish to hide (1). Then go to its Visible property (2). Finally, set the property’s function to the variable you initialized for it (3). In this example I decided I would use “cVisible” for “DataSecurityRiskCompleted”.

Click to enlarge

Repeat the steps above, assigning variables to each field you want to hide until a dropdown is changed to the triggering value.

Set the dropdown to toggle the related variable

The last step is to tell the variables we established to change based on a dropdown.

First select the DataCardValue (not the DataCard) within the data card (1).

Then select the OnChange property (2) and set the function to the following (3):

Switch(DataCardValue46.Selected.Value,"Yes",UpdateContext({cVisible: true}),UpdateContext({cVisible: false}))
Click to enlarge

Repeat for each dropdown that should serve as a trigger for conditional fields. Now, when I publish my app, changing “Data Security Risk” to “Yes” will change the default false visibility of DataSecurityRiskCompleted to true. If I change the dropdown to “No” again, it will hide the related field again.

Posted on

Alertus integration in SharePoint via Microsoft Flow

I’m by no means an HTTP request expert, but the requirement of being able to integrate business applications is common and, in this case, important. I took some time to figure out how we could implement Alertus integration and am sharing what worked here.

Background

Alertus is a program we use at LMH Health to push mass-notification desktop and mobile alerts to the organization during times of severe weather, evacuation procedures, or just software downtime.

Image from alertus.com

We use SharePoint as a supplement for “breaking news” by adding a banner to the top of our site during times of urgency. We wanted to integrate Alertus with SharePoint so that when an alert fired, it created a breaking news banner in SharePoint as well. This makes sure users who aren’t on a managed device, such as working from a home computer, are still able to get important news if they happen to be on the intranet.

In times of emergency, it’s best to utilize as many channels as possible to inform your users. Integration of these two services meant there’d be minimal delay in broadcasting a single message through all available channels.

How it works

To send the Alertus alert data to SharePoint, we needed something capable of receiving an HTTP request so Flow came to mind first. Once configured, your Alertus administrators will just send alerts as usual, being sure to include SharePoint in the alert profile.

  1. Create and send an alert in Alertus
  2. Flow receives HTTP request with alert data
  3. Flow creates item in SharePoint list used for breaking news

And, of course, with Flow we could then do any number of actions including additional HTTP actions, sending to email or phone, etc.

Note: This works for SharePoint server and SharePoint online. If using server, you’ll need the on-premises data gateway so that Flow can connect to your environment.

Setting it up

We need to do a few things to get this working:

1. Start creating a Flow (to get the HTTP POST URL)
2. Create the Alertus Service for “SharePoint”
3. Add the Alertus Service “SharePoint” to any Alert profiles for which you want to include SharePoint for distribution
4. Finish the Flow
5. Test

Start creating a Flow

1. Create a new Flow with the When a HTTP request is received trigger

2. Paste the following into the Request Body JSON Schema

{
    "type": "object",
    "properties": {
        "Content-Type": {
            "type": "application/json"
        },
        "id": {
            "type": "string"
        },
        "clientName": {
            "type": "string"
        },
        "clientVersion": {
            "type": "string"
        },
        "sender": {
            "type": "string"
        },
        "message": {
            "type": "string"
        },
        "sentDateTime": {
            "type": "string"
        },
        "expires": {
            "type": "string"
        }
    }
}

3. Expand Show advanced options and make sure method is POST

4. Copy the HTTP POST URL at the top of the step using the copy icon

Create an Alertus alert service for SharePoint

1. Go to Alertus, then click Configure System > Alert Services

2. Click +Add Alert Service

3. Select HTTP Request for Service Type, then name and describe the service.

4. Configure the rest of the alert service as follows, pasting in the content provided below

Click to enlarge

Activation Headers

Accept: application/json, text/plain
Content-Type: application/com.alertus-v1.0+json

Activation Body

{ "id":"${alert.originId}",
 "clientName":"${alert.clientName}",
 "sender":"${alert.sender}",
 "message":"${alert.text}",
 "sentDateTime":"${alert.sentDate.yyyy-MM-dd'T'HH:mm}",
"expires":"${alert.expirationDate.yyyy-MM-dd'T'HH:mm}"
}

5. Click Save Alert Service

Add the service to alert profiles and/or preset alerts (optional)

Your service is now available to be added to alert profiles (pre-selected sets of services to notify under certain circumstances, such as an evacuation) and preset alerts (customized alerts and notification groups).

Alert profile example of adding the new SharePoint service we created
Preset alert example of adding the new SharePoint service as a delivery method for the alert

You could, alternatively, choose not to include it in anything preset and only use it manually upon creation/configuration of an alert.

Finish the Flow

1. Go back to the Flow you started.

2. Add a HTTP Response step and make sure the Status Code is set to 200. This lets Alertus know the request was received and prevents an error from occurring on the Alertus side.

3. Add a SharePoint: Create item step and connect to the site and list for which you want to create an item from the alert details.

Test

From Alertus, do a Custom Activation of a test message just for the SharePoint service/delivery method.

In the example below, I also threw in an email alert as a step but you can see the whole process in Flow’s run history still only took 2 seconds.

Check in SharePoint that the item was added. In my case, we use custom script to display the most recent list item as an active alert.

Additional Configuration (optional)

Now that you’ve done the basics, you may wish to make adjustments.

  • Check out Alertus’ Knowledgebase (must be a customer with login credentials) at https://my.alertus.com/ .
    • Search for HTTP Request (Documentation) where you’ll find additional placeholder variables you could use in the Activation Body and JSON Schema fields to send additional details.
  • Add additional steps in Flow
    • An approval process before posting the details
    • An email or text message to a person or group
    • Update a spreadsheet/log
    • Post a message to Teams
Posted on

Capitalize whole words or first letters of each word in Excel cells

Since Excel doesn’t have the same set of robust text transformation tools as Word, we need creative ways to get the same effects sometimes. When it comes to auto-capitalizing whole words or first letters of words, there are two functions you can use:

  • =UPPER() will capitalize the entire cell contents
  • =PROPER() will capitalize first letters of each word in a cell

In the GIFs below, you’ll notice I demonstrate both. The steps for both are exactly the same, so just pick the formula/function you need.

Since nobody wants to add another column to a sheet unnecessarily, we’ll just temporarily create a column for the calculation, copy the result, then delete it. You may, alternatively, wish to just hide it for later use or keep it alongside the original for a purpose.

Insert column and formula

  1. Insert column
  2. Place formula of choice in top cell of column and drag down (lower right-hand box/handle of cell) or press enter if in table format as seen below
Click to enlarge

Copy new values over originals and delete temporary column

Next we’ll use the “Paste values” feature to remove the formula dependency of the resulting data and keep just the newly formatted text.

“Paste Values” icon when pasting in Excel. This option copies just the values resulting from a formula so you don’t “break” your data/calculations when moving/deleting.
  1. Copy values
  2. Paste (special) values only over original column values to replace them with the correct capitalization version
  3. Delete temporary column
Click to enlarge
Posted on

Filter even or odd rows in Excel

This morning I pasted a list of values into Excel in which the pattern consisted of a “good value” followed by that same value with a minor adjustment. The second, near-duplicate value wasn’t useful to me so I wanted to filter out all even rows so I’d just have the first of each near-duplicate.

I say near-duplicate because if your values truly are duplicates, you can just use the Remove Duplicates function.

So to filter odds and evens, we’ll add a new column to the right of our data and set the formula to:

=ISEVEN(ROW())

This evaluates the data’s row number as even or odd and will return TRUE if the number is even or FALSE if not. Double-click the handle in the lower right of the cell to fill down.

Then we filter the column for just TRUE (evens) or FALSE (odds). You can right-click the value you want to keep > Filter > Filter by Selected Cell’s Value

Click to enlarge