How to use service accounts in Power Automate flows and avoid common pitfalls

Do you want to automate your workflows with Power Automate and make sure they can last for years to come? If so, you need to pay attention to how you share your flows and how you sign into the services that you use in them. Using your own user account may seem convenient, but it can also cause trouble if your account gets changed or deleted. In this post, I will show you two examples of what can go wrong when you use personal accounts in Power Automate flows and how to avoid them by using a service account instead.

Consider these two scenarios involving Power Automate flows that connect to different services:

  • Bob creates a flow that triggers when a new item is added to a SharePoint list and sends an email alert to his team. He uses his own user account to create the flow and does not share it with anyone else. A few months later, Bob quits his job and his account is disabled. The flow stops functioning because the connection is lost and no one can access or edit it. The team does not receive any email notifications and has to manually monitor the SharePoint list for new items.
  • Carol creates a similar flow as Bob but she shares it with her colleague Dave as a co-owner. However, she still uses her own user account to authenticate to some connectors or actions within the flow, such as sending emails from her Outlook account. A few months later, Carol resigns from her job and her account is deactivated. The flow still runs but some connectors or actions fail because they rely on Carol’s credentials. The team receives incomplete or erroneous email notifications.

These examples demonstrate the risks of using personal accounts in Power Automate flows. And both scenarios could have been avoided if Bob and Carol had used a service account to create their flows and connect to the various services used in the flow. You may end up with broken connections, failed actions, or loss of access otherwise.

So what exactly is a service account? A service account is a special user account that has a fixed license and does not belong to any individual person. Multiple users may know/share the credentials to use the account for building flows. You’ll treat it much like a user account as you provision a license for it then add it to teams, groups, and roles so that it can perform actions a normal user would.

By using a service account, you can make sure that your flows run reliably and securely no matter what happens in your organization or team. Just be careful, as with any shared security asset, not to share a service account’s credentials with too many flow makers. 2-3 users would be the max I would personally consider for redundancy as people take leave and the organization experiences turnover.

Tips for building sturdier flows in Power Automate by using service accounts

  • Create a dedicated service account with a fixed license and assign it the appropriate roles and permissions (site or team membership, shared mailbox member, security group member, etc.)
  • Consider creating multiple service accounts that have access to different environments, teams, sites, etc. to ensure you don’t have one service account with access to absolutely everything
  • Creating the flow
    • Use a service account to create the flow or
    • Use a service account to create the flow and share it with other users as co-owners for more convenient edit access or
    • Share a flow you have already created with the service account as a co-owner (co-owners keep flows after other co-owners leave an organization)
  • Use the service account to authenticate the connectors or actions that you use in your flows, such as SharePoint, Outlook, or Teams. If any single step’s connection belongs to an individual, there is risk that the step will fail if that person’s account is disabled or even if they have authentication issues.
  • Test your flows regularly and monitor their performance and status using the service account
  • Document your flows and their connections and keep track of any changes or updates using the service account

References

Supercharge your SharePoint pages and news with these 10 popular web parts

SharePoint is the leading digital workplace platform that lets you create, manage, and share content with your team and your audience (among many other abilities). One of the best features for everyday users when creating content in SharePoint is the ability to easily add web parts to your pages and news. Web parts are like building blocks that let you add different features and functions in a single context alongside other building blocks – for example, Documents next to instructional text or policy reminders for using the library.

But how do you choose the right web parts for your pages and news? Well, it depends on what you want to do and who you want to reach. You should pick web parts that help you achieve your goals and communicate your message clearly and effectively. You should also keep your page design simple and neat, using only the web parts that you really need.

The add web part dialog | Click to enlarge

Here are some of the best web parts to use when creating SharePoint pages and news:

Web PartWhat it does

Highlighted content
Shows content that matches certain criteria or attributes, such as content type, location, custom metdata, or search term. I like this web part a lot because it gives me plenty of flexibility and helps me surface the right content when I need it.

News
Displays the latest news on your SharePoint page, and can include news from multiple sites as well.

Hero
Shows up to five items with images, text, and links to make them stand out. You can use it to add some flair and visual interest to your SharePoint page.

Events
Creates and shows events on your page. You can use it to share important dates, deadlines, meetings, or celebrations with your audience.

Quick links
Adds links to your page for easy access. You can use it to direct your audience to other pages, sites, or external resources that are relevant to your content.

People
Shows information about people on your page. You can use it to introduce your team members, collaborators, or contacts to your audience. You can also display their profile pictures, names, roles, and contact information.

Document library
Shows documents on your page. You can use it to upload or link to files that you want to share with your audience. You can set it to only show a specific view of a library to manage your space better (perhaps a view with minimal columns shown).

List
Displays a list from your site. This surfaces a view of a particular list as a web part. Consider using views that filter a person field to [Me] (the signed in user). This is a great way to personalize pages (your requests, your tasks, etc.) and surface relevant information alongside related resources (countdowns, calendars, files, policies, etc.).

File viewer
Shows files on your page. You can use it to display files such as videos, images, PDFs, or Microsoft 365 app files like Word, Excel, or PowerPoint files. You can also adjust the size and position of the file viewer on the page.

Embed
Shows content from other sites that allow embedding. You can use it to add content from sites like YouTube, Twitter, or Spotify to your page. You can also customize the appearance and behavior of the embedded content on the page.
Table of 10 SharePoint Online web parts to consider for your pages and news

These are just some of the many web parts that you can use in SharePoint. Want a fuller list? Check out my other post: SharePoint team vs communication site web part options.

Each web part can be customized to suit your needs and preferences. Ready to start building better pages? Check out this video, Create pages in SharePoint Online sites, to see the process:

References

5 essential settings to consider for every team in Microsoft Teams

Microsoft Teams is a powerful tool for teamwork and collaboration that has transformed the landscape of digital work. You can create and join teams (groups of people and their shared tools and work), chat with others within and beyond your organization, share and manage files, and much more. However, to make the most of Teams, you need to configure some settings and permissions for your team on a case-by-case basis. In this blog post, I will highlight five important settings to consider for each of your teams in Microsoft Teams.

Not a team owner (yet)? Share these topics with whoever is or keep them in mind for your future teams. I’ll cover:

  1. Team name and description
  2. Team privacy
  3. Team members and owners
  4. Team channels and tabs
  5. Team notifications and mentions

1. Team name and description

The team name and description are the first things that people see when they browse or search for teams. They should be clear, concise, and informative. You can change the team name and description by going to the team name, clicking More options | Edit team, and typing in the new name and description.

Consider using a naming convention to make it clear which region, department, audience, etc. a team belongs to. For example, perhaps an EXT_ prefix will always preceed teams with external participants. Learn more about team naming conventions here: The Importance of a Teams Naming Convention | Microsoft Learn

Edit team name and description steps | Click to enlarge

You can also add a team picture to make your team more instantly recognizable.

Steps to modify the team picture | Click to enlarge

2. Team privacy

The team privacy determines who can see and join your team. You can choose from two options: public or private. A public team is visible to anyone in your organization, and anyone can join it without approval. A private team is only visible to team members, and people need to request to join it or be invited by a team owner. You can change the team privacy by going to the team name, clicking More options | Edit team, and selecting the privacy option.

Team privacy setting | Click to enlarge

3. Team members and owners

The team members and owners are the people who belong to your team. The team members can chat, share files, and participate in meetings. The team owners can manage the team settings and permissions, add or remove members, and delete the team. You can add or remove team members as well as promote or demote roles (i.e. from member to owner) by going to the team name, clicking More options | Manage team, and selecting the Members tab.

Members settings for a team in Microsoft Teams | Click to enlarge

4. Team channels and tabs

The team channels and tabs are the spaces where you can have conversations and access tools within your team. The team channels are typically organized by topics, projects, or departments. You can create standard channels that are open to everyone in the team, or private channels that are only accessible to a subset of team members. You can add or remove team channels by going to the team name, clicking More options | Add channel or More options | Manage team, and selecting the Channels tab. Note that the General channel comes with every team and cannot be deleted.

You can also edit individual channel names, descriptions, and whether or not it’s shown or hidden by clicking More options | Edit this channel.

The team tabs along the top of each channel are the shortcuts to your favorite apps and tools within a channel. You can add tabs for a Planner board, a Power BI dashboard, a OneNote notebook, and more. You can add or remove team tabs by going to a channel, clicking the + sign at the top, and choosing the app or tool you want to add.

The add a tab dialog where you can choose an app | Click to enlarge

You can also reorder the tabs by dragging and dropping them, or delete the tabs by selecting it’s dropdown arrow and choosing Remove.

5. Team notifications and mentions

The team notifications and mentions are two ways to stay updated and alert your team members. The team notifications are the alerts that you receive when something happens in your team, such as a new message, a new file, or a new meeting. You can customize your team notifications by going to Settings and more (…) and then Settings | Notifications, and choosing the notification level and sound for each activity.

Notification settings for the Microsoft Teams client app | Click to enlarge

The team mentions are the tags that you use to get someone’s attention in a conversation. You can mention a person, a channel, a tag, or the whole team by typing @ followed by their name. The team mentions help you communicate effectively and efficiently with your team.

Learn about all four ways to @ mention in Teams, and when to use which, in my other post (includes video): 4 ways to @mention in Microsoft Teams to get attention on your posts and replies

You can also send a message as important, indicating visually that it’s a high priority.

Steps to mark a message as important in Microsoft Teams | Click to enlarge

Learn about more permissions and settings

There are, of course, many more than 5 settings to consider for each of your teams. While I have highlighted specific topics and considerations, I encourage you to keep learning with these resources:

How to change your profile picture in Microsoft Teams

Using a profile picture in collaborative apps like Microsoft Teams can enhance communication, recognition, engagement, and social connection among coworkers. It can also remove anonymity and add a human touch to the digital workplace.

Updating your profile picture in Microsoft Teams updates your profile picture across all of Microsoft 365 – the same picture will appear when you’re using Office.com, SharePoint, OneDrive, Outlook, etc. in the upper right corner.

General location of your profile pic in M365 apps other than Teams | Click to enlarge

Ready to give it a try? Follow these steps:

How to update your profile picture in Microsoft Teams

  1. Launch Microsoft Teams on your device.
  2. Tap or click on your current profile picture at the top left or right of the screen, depending on your device
  3. Tap or click on the camera icon that appears when you hover over or tap on your profile picture.
Steps to update your profile pic in Microsoft Teams desktop app | Click to enlarge
  1. Tap or click on Upload picture to access your photos.
Upload picture option in the Microsoft Teams desktop app | Click to enlarge
  1. Select a .png, .jpg, or .gif image that’s less than 4 MB in size and tap or click on Save.

And that’s it! It may take a small while to see the new photo reflected everywhere throughout Microsoft 365, but there’s nothing more to do. 🎉

Here’s a video demonstration of how to update your profile picture using the Microsoft Teams mobile app:

How to schedule SharePoint pages and news posts to publish on a specific date and time

Sometimes you may have SharePoint news posts edited and ready to go, but have to wait for an official announcement or date to arrive before you can share the news. Rather than wait for that date and manually publish, we can schedule news posts to go live at a date and time we specify.

In order for this to work, we have to turn on a setting for the Site Pages library in which we’re creating the news.

Note

Only site owners can enable page and news publishing for their site. If you’re a site member, ask your site owner to follow the steps in the first section.

How to enable scheduling for your site’s SharePoint pages and news

  1. Go to your Site Contents (settings wheel | Site Contents) and select your Site Pages library (this is where your site’s pages and news are created and stored).
Site Pages library location in SharePoint | Click to enlarge
  1. Select Scheduling at the top
Scheduling option at the top of the Site Pages library | Click to enlarge
  1. Toggle on the Enable scheduling option

Now your site’s members can use the scheduling feature for their pages and news. The next section shares user steps to do so.

How to schedule SharePoint pages and news

  1. Create your page or news post as you normally would (Site’s home page | New | Page or News post)
Location of new page and news post options on a site | Click to enlarge
  1. When you’re ready to schedule the post, click Page details | Enable Scheduling and add the Publish Start Date/time | Schedule (this button changes from Post/Publish to Schedule after you’ve entered the publish start date)
Scheduling steps for SharePoint pages and news | Click to enlarge

How to live stream a Microsoft Teams meeting to YouTube without installing additional software

Live streaming Teams meetings and webinars to YouTube may expand your audience and allow for more viewers to engage with your event than otherwise might. It’s great for running a virtual conference or event where you need to embed YouTube videos in the platform for a streamlined approach. YouTube also saves your live streams as videos in your channel without the need to do a separate upload after an event. This concept gives users/presenters the ease of just having a normal Teams meeting, but the power of enhanced broadcasting and distribution.

Though I’m focused on YouTube in this blog post, the same setup on the Teams side is required to live stream to social media platforms or otherwise.

Thanks to recent updates to Microsoft Teams, you no longer need OBS Studio or other third-party software installations to live stream your Microsoft Teams events to YouTube. However, this ability launched in disabled state by default, so a Teams Administrator will need to complete the prerequisites prior to someone being able to live stream.

Note

At this time, live streaming only works for meetings and webinars in Microsoft Teams – not live events.

Admin prerequisites to live streaming with Microsoft Teams

Before users can broadcast their live stream from Microsoft Teams, a Teams administrator needs to create or modify a Meeting policy and:

  • Enable Live Streaming Mode (General section)
  • Turn on Local Broadcasting (Audio & video section)

You can modify the Global (Org-wide) default Meeting policy which would allow everyone to broadcast and live stream, or you can create/modify a separate Meeting policy and apply it only to specific users who should be allowed to live stream.

Here’s how to modify the Global (Org-wide) default meeting policy to allow everyone in your org to live stream:

  1. Go to the Teams admin center (https://admin.teams.microsoft.com/)
  2. Select Meetings | Meeting policies
  3. Select the name of the policy you wish to modify. In this example, Global (Org-wide) default).
Location of the global/default meeting policy in the Microsoft Teams Admin Center | Click to enlarge
  1. Enable Live streaming mode, and turn on Local broadcasting as seen in the following screenshot.
Live streaming settings in a Meeting policy | Click to enlarge

Technically, you could just enable Live streaming mode for this post. But by also turning on Local broadcasting (NDI), your users will have greater flexibility to produce their Microsoft Teams events with broadcasting software like OBS Studio, XSplit, etc. which gives additional capabilities like setting up scenes and such.

  1. Click Save

Note

The changes may take an hour or so to take effect after saving your policy changes.

Your users will now be able to live stream their Teams events. Now let’s go through those steps.

Producer prerequisites to live streaming with Microsoft Teams

If you are going to be the producer of an upcoming event, you’ll need to make a change to your individual Teams client settings before you’re able to proceed.

Simply open your settings in Teams (ellipsis/three dots by your profile picture | Settings) and then select App permissions. Here you can enable NDI capabilities.

NDI capabilities enabled in the Teams client settings | Click to enlarge

How to live stream a Microsoft Teams meeting or webinar to YouTube

Users assigned the policy created or modified in the admin prerequisite section can follow these steps to live stream directly to YouTube from Microsoft Teams.

  1. First we need to set up the live event on the YouTube side. Go to YouTube Studio (https://studio.youtube.com) and sign in with the account you wish to broadcast to.
  2. Click Create | Go live in the upper right
YouTube steps to Go live | Click to enlarge
  1. Choose whether you’re going live Right now, or whether you’re just setting up for a Later date. Either way, you’ll have additional steps that may vary from this post – follow the prompts to complete setup.
Go live options in YouTube | Click to enlarge
  1. Continue through your YouTube prompts but do not go live yet. When you get out of the wizard, be sure you’re on the Stream tab on the left-hand navigation and locate the Stream key and Stream URL. You’ll need to copy both of these and use them later in step 9.
YouTube stream key and stream URL locations | Click to enlarge
  1. Now we need to switch to Microsoft Teams. Join the meeting or webinar you wish to live stream.
  2. Add the Custom Streaming app to the meeting
Steps to add the Custom Streaming app to a Teams meeting | Click to enlarge
  1. Click Add
Custom Streaming app info and confirmation screen | Click to enlarge
  1. Click Save
Custom Streaming app welcome screen | Click to enlarge
  1. In the right-hand panel that opens, paste the Stream key and Stream URL you copied in step 4.
Copy/paste paths for stream key and URL | Click to enlarge
  1. Click Start streaming in the lower right, then select Allow in the dialog box when it appears.
Streaming meeting confirmation dialog | Click to enlarge
  1. You’re now live streaming! Share your screen and/or use your cameras and microphones to run your event as you would any normal Microsoft Teams meeting.
A screenshare from Microsoft Teams being live streamed to YouTube | Click to enlarge
  1. When you’re finished with the event, you can stop streaming via Teams and YouTube.
Stop streaming button in the Custom Streaming app panel | Click to enlarge
End stream button in YouTube | Click to enlarge

How to reset a SharePoint column value to blank using Power Automate

In Power Automate, setting an already-populated SharePoint list or library field to blank isn’t as simple as leaving the field blank in your flow. Luckily, it’s just a couple extra steps to make it possible.

In this post I’ll cover multiple column types as they’re a bit different.

  • How to set text, date, number, and yes/no column values to blank
  • How to set single choice column values to blank
  • How to reset multi-choice column values to blank
  • How to reset person column values to blank

How to set SharePoint text, date, number, and yes/no column values to blank

The steps in this section will work for these column types:

  • Single line of text
  • Multiline text
  • Date
  • Number
  • Yes/No
  1. Click into the field you want to reset to blank
  2. Select Expression
  3. Type Null in the expression box (not case-sensitive)
  4. Click OK
Adding a Null expression in Power Automate | Click to enlarge

Test your flow and it should reset the field value(s) to blank wherever you used the Null expression.

Showing the fields that were reset using Power Automate | Click to enlarge

How to set SharePoint single choice field values to blank

To set a choice field back to blank (no selection) follow these steps:

  1. Add a step before updating the item that initializes a variable. The step is called Initialize Variable.
  2. Set the variable name to Blank, and the type to String. Do not set a value.
  3. In your update action, set the choice field value to the new variable from dynamic content.
Creating a blank variable in Power Automate | Click to enlarge

Test your flow, and you should see your single choice field reset to no choice.

Result of a Power Automate flow resetting a choice field | Click to enlarge

How to reset SharePoint multi-choice column values to blank

A normal choice column in SharePoint has more options that allow it to be set to multiple choice.

Multiple selection option for a choice field in SharePoint | Click to enlarge

Changing a single-select choice column to a multi-choice field alters how Power Automate resets the field, but it’s still similar to the steps involved for a single choice field.

To set a multiple-choice field back to blank (no selections) follow these steps:

  1. Add a step before updating the item that initializes a variable. The step is called Initialize Variable.
  2. Set the variable name to BlankArray, and the type to Array. Do not set a value.
  3. In your update action, select the T icon next to your multiple-choice field.
  1. Set the multi-choice field value to the new variable from dynamic content.
Power Automate actions resetting a multiple-choice field to blank | Click to enlarge

Test your flow and your multi-choice fields should now reset to blank.

Result of a Power Automate flow resetting multiple-choice fields to blank | Click to enlarge

How to reset SharePoint person column values to blank

Perhaps one of the more complicated column types to reset, a person field takes a bit more work to reset. To accomplish this, we can use an HTTP request. Follow these steps to empty a SharePoint person column’s value.

  1. Add the Send an HTTP request to SharePoint step
  2. Following the screenshot below, set the fields as follows (you must replace all red items with your own values):
    • Site Address (select your site)
    • Method: POST
    • Uri: _api/web/lists/getbytitle(‘YOUR LIST TITLE‘)/items(YOUR ITEM ID FROM DYNAMIC CONTENT)
    • Headers
      • IF-MATCH | *
      • X-HTTP-Method | MERGE
      • Content-Type | application/json;odata=verbose
      • Accept | application/json;odata=verbose
    • Body:
      {
      “__metadata”:{
      “type”:”SP.Data.YOUR_x0020_LIST_x0020_TITLEListItem”
      },
      YOURPERSONFIELDId”:”-1″
      }

Important notes for the Body field

If your list name has a space, replace it with _x0020_ such as Null_x0020_demo.

Field names are different than display names. Even if your field displays with a space (Person field) it is likely Personfield on the backend. You can verify your column’s actual name by going to List settings (gear icon) and selecting your column from the Columns section. Your field’s actual name will be displayed in your browser’s URL.

How your HTTP request should look when finished | Click to enlarge

Test your flow and you’ll find that your person column has been reset.

How to generate random sample paragraphs using Microsoft Word

There are many web tools out there to generate random “lorem ipsum” filler content, but you can do the same thing using Microsoft Word. Simply:

  1. Type =RAND(5) on a line
  2. Hit enter

Instantly, the formula is replaced with five paragraphs of sample content. Replace 5 in the formula with the number of paragraphs you wish you to generate.

See a video demonstration here:

Video demonstration on YouTube

Shoutout to Jaime Velez for telling me about this for the first time. Game-changer!

Want additional tools for different types of sample content? Try the following:

How to get a distinct count of unique values in Excel

Note: Video demonstration at bottom of post.

In some applications, like Power BI, you can get a distinct count of unique values in a column in just a click or two. But of all the functions in Excel, there isn’t a straightforward “Distinct Count” unfortunately.

There are, however, the following two functions that, when used together, give the desired result:

  • COUNTA – Counts the number of cells in a range that are not empty
  • UNIQUE – Returns the unique values from a range or array

So, we’re listing all the unique values we have in a range or column by using UNIQUE and then nesting it within the COUNTA function to count the number of values returned. Here are three examples:

Distinct count goalFormula example
Distinct count for a range of cells=COUNTA(UNIQUE(E19:E26))
Distinct count for a table column when referenced within the same table=COUNTA(UNIQUE([Project]))
Distinct count for a table column when referenced outside the table=COUNTA(UNIQUE(Table1[Project]))

Be sure to replace the cell range, column name, and/or table and column name in the formulas with your own.

A video demonstration of this formula in use is embedded below.

Celebrating four years as a Microsoft MVP in Microsoft 365 apps and services

This is the fourth consecutive year I’ve been awarded as a Microsoft MVP in the Microsoft 365 Apps & Services category. 🤩

What does that mean?

Microsoft presents the MVP award annually to individuals who have regularly made high-quality contributions to the tech community. Sometimes this is forum participation, conference and user group presentations, books, blogs, videos, code and solutions, social media, or any number of other ways a person has evangelized and helped drive adoption and understanding of Microsoft 365.

I focus my contributions on Microsoft 365 productivity and collaboration apps like OneDrive, SharePoint, Teams, Power Automate, Power BI, and more. Specifically:

And all of that’s in addition to my regular, full-time day job.

To become an MVP, start tracking your contributions and reach out to an existing MVP or Microsoft employee that you’ve established a relationship with to secure a nomination. If we’ve crossed paths or worked together, I would be honored to nominate you.

It has been a true pleasure and privilege to be a part of this amazing community for four years now. Here’s to the next year of content, experiences, and connections. 🥳🥂