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

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.

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.

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

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

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

Remove duplicate values from an Excel column

A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.

1. Select the column, or the values from which you’re removing duplicates.
Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog.
2. Go to Data > Remove duplicates (in the Data Tools panel)

3. Confirm if your column has a header (column title or not) and click OK.

4. Excel will confirm the number of duplicates found and how many remain. Click OK.

Adjust column widths in classic SharePoint lists using CSS (including “Quick Edit”/datasheet views)

Once upon a time two years ago (two years?!?) I shared how you can adjust SharePoint column widths in traditional views using JQuery.

But adjusting the same widths in datasheet mode (quick edit, for example) is a bit different. After a bit of fiddling around, I found an answer that will allow you to adjust column widths for both standard and datasheet view types using just CSS.

The difference is in how you reference the column names in the css:

  • Standard (catches both filterable and non-filterable columns such as multi-line text): th.ms-vh2-nofilter div[DisplayName=’Column1′],th.ms-vh2 div[DisplayName=’Column1′]{…
  • Datasheet: th[Title=’Column1′] {…

Everything that follows that first line is the same in both types of views, fixing a minimum and regular width property for the column(s).

Standard Views (not quick edit)

<style>
th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

Datasheet/Quick Edit Views

<style>
th[Title='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

All Views

So if you’re including just one script reference for all views in your list, you’d be safe to include all formats in the script.

<style>
th[Title='Justification'],th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

Multiple columns

For multiple column width adjustments, just include another block for each column as seen here:

<style>
th[Title='Justification'],th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
th[Title='Other Column'],th.ms-vh2-nofilter div[DisplayName='Other Column'],th.ms-vh2 div[DisplayName='Other Column']{
    min-width:200px!important;
    width:200px!important;
}
</style>

Join me at SharePoint Fest Seattle Aug 19-23, 2019

I’m honored to be among many fantastic presenters for SharePoint Fest Seattle. When first learning SharePoint, SharePoint Fest Seattle was the first big conference I attended. I was an administrative associate at the time. I remember attending a workshop on SharePoint Designer workflows by Vlad Catrinescu and thinking it was the coolest thing I’d ever seen.

Each night of the conference, I’d stay up late working on things I’d learned or ideas I’d gotten during the day. After the conference, I dove all in and ended up working full-time in SharePoint. My job transformed into a SharePoint Business Analyst role. Fast forward, now I’m a Microsoft MVP, Microsoft 365 Certified Enterprise Administrator Expert, author, blogger, user group leader, and a SharePoint Systems Engineer at LMH Health.

I’m continually wowed and re-energized by all that’s coming out of Microsoft. And I’m excited to be on the other side of the podium for a few sessions this year, sharing some of my favorite topics. And, as always, I’m looking forward to sitting in the audience as well for what is certainly one of the year’s best professional development opportunities.

Come discover how SharePoint Fest Seattle can expedite your career growth through professional development and inspiration. Register today at https://www.sharepointfest.com/Seattle/ and use code Chamberlain100 to save $100 off your pass.

I’ll be presenting these three sessions, as well as sitting at the “Ask the Experts” booth a couple times. I hope I’ll see you there!

INT102 – Building the Intranet of the Future: Using SharePoint to Empower Collaboration

Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint. 

We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets, rethinking forms and minimizing busy work by utilizing automation capabilities in SPD workflows and Microsoft Flow and designing intuitive navigation.

CM202 – SharePoint wizardry for content management, archiving & retention

Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”? 

Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!

BV303 – You’re the one, OneNote!

Are you looking for more productivity in your life? Sticky notes leaving you…unsatisfied? 

Sit back and enjoy this journey through the sweetest productivity story ever told, where you’ll be inspired to take “note” of new ways of working more efficiently day-to-day with OneNote. When it comes to working smarter and not harder, OneNote is the one.

Solution: “The embed code is invalid because the source of the embed content is not allowed” error when embedding Microsoft Stream video in SharePoint

A user recently ran into the following error when attempting to embed a Microsoft Stream video on a SharePoint 2016 site:

“The embed code is invalid because the source of the embed content is not allowed.”

To resolve this, you must be a site collection administrator.

Go to site settings and select “HTML Field Security” under “Site Collection Administration.”

By default, SharePoint allows embeds from YouTube, Bing, Vimeo, and Microsoft but we need to add web.microsoftstream.com to the list.

Now try to embed your video again, and it will work.