Update a hyperlink or picture column in SharePoint using Microsoft Power Automate (Flow)

Setting a hyperlink column’s value using Power Automate is a bit different than setting other column types’ values. In SharePoint, a hyperlink column has two components – address and description. If you update this column type using Power Automate’s “Update item” action, your address and description are both set to the same value.

In SharePoint Designer workflows, we could do this easily with the usual “Set field” action:

But in Power Automate, we only get one field which maps to the URL/address part of the hyperlink field, and is duplicated as the description as well in SharePoint.

And if you’re thinking it could work with a comma, as some other field types might, it won’t. You’ll get “Enter a valid uri.”

To get this to work so we can set both address AND description as separate values, we have to use an HTTP request action instead of (or in addition to) the Update item action. This isn’t as complicated as it may sound. Here are the steps:

1. Add the Send an HTTP request to SharePoint action

2. Choose or enter your site for Site Address

3. Set Method to POST

4. Set the Uri to the following, changing List Name to your list’s name, and replacing [ID] with the ID field from dynamic content

_api/web/lists/GetByTitle('List Name')/items([ID])

5. The easiest way to set your headers is to copy and paste this text into the “Text mode” option as demonstrated in the GIF that follows.

{
   "Content-Type": "application/json;odata=verbose",
   "X-HTTP-Method": "MERGE",
   "IF-MATCH": "*"
 }
Click to enlarge

6. Lastly, in Body, you can copy and paste this as well, but:

  • replace List_x0020_Name with your own list’s name. _x0020_ should replace any spaces in your list name.
  • replace Hyperlink with the name of the hyperlink column’s name
  • replace Google with the label/description/clickable text you want for the hyperlink
  • replace http://www.google.com with the URL/address part of the hyperlink
{'__metadata': {'type':'SP.Data.List_x0020_NameListItem'},'Hyperlink':
 {'Description': 'Google',
 'Url': 'http://www.google.com'}
 }

Your final result should resemble this:

Click to enlarge

Test that out. When it’s working and you’re ready to take it to the next level, you can replace the static url (like Google’s in my example) with dynamic content. For example, you could create convenient one-click links to an item’s version history:

Click to enlarge
Click to enlarge

Solution: Column validation error “Sorry, something went wrong. The formula cannot refer to another column.” when referencing today’s date

If you’re trying to create column validation using [Today], you’ll likely get the following error:

Sorry, something went wrong. The formula cannot refer to another column. Check the formula for spelling mistakes or update the formula to reference only this column.

Commit this to memory: when [Today] or Today fails in a formula, try Today().

While [Today] may work in list view filters, it’s not the correct format for column validation or calculated columns. You can, instead, use Today() for column validation.

For example, you could use EndDate<=Today() to require users to enter an EndDate that is on or before the date of submission.

Note that if you’re creating column validation through the modern UI, you’ll need to start validation formulas with an equals sign.

SharePoint column validation format difference between classic and modern experiences

The following column validation formula worked fine in SharePoint on-prem (2016 specifically, in my case), but returned an error when used in the exact same context in SharePoint Online’s modern UI:

EndDate<=Today()

The expected behavior, in SharePoint Server/on-prem OR SharePoint Online/O365, is that if someone enters a date beyond the current date, they’ll get an error message and cannot submit the form until it’s corrected and the validation formula resolves to TRUE.

Troubleshooting in SharePoint Online

I used this formula in SharePoint Server/on-prem, and it worked fine. Then I tried using the modern UI in SharePoint Online by using the column’s menu > Column settings > Edit.

But when you try to save the exact same formula (specifically from the modern experience side panel) you get the error “A formula has a syntax error.”

Then I decided to try the classic view of settings to compare on-prem and online as closely as possible. I went to Settings > List settings and selected my column.

And, as you already know, it WORKED when entered on this classic column settings page (in SharePoint Online still) instead of the modern column settings side panel accessed directly from the list view.

When I go back through the modern UI now that my formula saved successfully, I see what caused the problem. The modern UI requires that you begin the formula with an equals sign (=). When I created the formula through the classic column settings method, it automatically added the equals sign for me in the background.

Solution

So if you’re creating column validation formulas in the modern experience (or even in classic), just remember to add an equals sign (=) to the start of your formula.

Click to enlarge

The difference is simply which formats are accepted.

  • Classic: Start formulas with or without equals sign
  • Modern: Start formulas with equals sign

Demystifying Microsoft 365 admin roles in Azure AD and the M365 admin center

As a rule of thumb (not to mention for improving your Secure Score), you should limit the number of people who have the “global admin” role in your organization. Microsoft recommends fewer than 5 global admins. That makes it important to get to know the other roles available and assign the least permissive role (a phrase you’ll see frequently if seeking certifications) rather than blanket roles that often include more permissions than what are necessary (or secure).

Global admins can assign other admin roles, purchase additional products and subscriptions, reset all (including each others’) passwords, and manage absolutely everything in your tenant. So of course you can see why we’d want to restrict how many are working with these capabilities simultaneously.

You may end up assigning five different, non-global admin roles to a user instead of the single global admin role, but your security will be improved significantly.

There are a couple places to assign admin roles: the Azure AD portal, and the M365 admin center. My goal with this post is to consolidate and simplify information on the roles, including which are only available in Azure. I’ve combined information from:

Those marked with * are only available to assign from Azure AD. All others are in both the M365 admin center AND the Azure portal.

Note: Most role descriptions are copied directly from the resources listed above as of date of publish and are subject to change. Always check Microsoft documentation prior to making significant decisions. 

Available roles

Full access to enterprise applications, application registrations, and application proxy settings.

> Read more about this role on docs.microsoft.com

Create application registrations and consent to app access on their own behalf.

> Read more about this role on docs.microsoft.com

Can require users to re-register authentication for non-password credentials, like MFA.

> Read more about this role on docs.microsoft.com

Can manage Azure DevOps organization policy and settings.

> Read more about this role on docs.microsoft.com

Manages labels for the Azure Information Protection policy, manages protection templates, and activates protection.

> Read more about this role on docs.microsoft.com

Can create and manage all aspects of user flows.

> Read more about this role on docs.microsoft.com

Can create and manage the attribute schema available to all user flows.

> Read more about this role on docs.microsoft.com

Can manage secrets for federation and encryption in the Identity Experience Framework.

> Read more about this role on docs.microsoft.com

Can create and manage trust framework policies in the Identity Experience Framework.

> Read more about this role on docs.microsoft.com

Makes purchases, manages subscriptions, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to enterprise applications and application registrations. No application proxy.

> Read more about this role on docs.microsoft.com

Manages regulatory requirements and eDiscovery cases, maintains data governance for locations, identities, and apps.

> Read more about this role on docs.microsoft.com

Manages Azure Active Directory conditional access settings, but not Exchange ActiveSync conditional access policy.

> Read more about this role on docs.microsoft.com

Manages Customer Lockbox requests, can turn Customer Lockbox on or off.

> Read more about this role on docs.microsoft.com

Can access and manage Desktop management tools and services.

> Read more about this role on docs.microsoft.com

Can read basic directory information. Commonly used to grant directory read access to applications and guests.

> Read more about this role on docs.microsoft.com

Do not use. This role is automatically assigned to the Azure AD Connect service, and is not intended or supported for any other use.

> Read more about this role on docs.microsoft.com

This is a legacy role that is to be assigned to applications that do not support the Consent Framework. It should not be assigned to any users.

> Read more about this role on docs.microsoft.com

Full access to Microsoft Dynamics 365 Online, manages service requests, monitors service health.

> Read more about this role on docs.microsoft.com

Full access to Exchange Online, creates and manages groups, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Configure identity providers for use in direct federation.

> Read more about this role on docs.microsoft.com

Has unlimited access to all management features and most data in all admin centers.

> Read more about this role on docs.microsoft.com

Has read-only access to all management features and most data in all admin centers.

> Read more about this role on docs.microsoft.com

Creates groups and manages all groups settings across admin centers.

> Read more about this role on docs.microsoft.com

Manages Azure Active Directory B2B guest user invitations.

> Read more about this role on docs.microsoft.com

Resets passwords and re-authenticates for all non-admins and some admin roles, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to Intune, manages users and devices to associate policies, creates and manages groups.

> Read more about this role on docs.microsoft.com

Full access to all Kaizala management features and data, manages service requests.

> Read more about this role on docs.microsoft.com

Assigns and removes licenses from users and edits their usage location.

> Read more about this role on docs.microsoft.com

Access to data privacy messages in Message center, gets email notifications.

> Read more about this role on docs.microsoft.com

Reads and shares regular messages in Message center, gets weekly email digests, has read-only access to users, groups, domains, and subscriptions.

> Read more about this role on docs.microsoft.com

Manages cloud-based policies for Office and the What’s New content that users see in their Office apps.

> Read more about this role on docs.microsoft.com

Can reset passwords for non-administrators and Password administrators.

> Read more about this role on docs.microsoft.com

Full access to Power BI management tasks, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to Microsoft Dynamics 365, PowerApps, data loss prevention policies, and Microsoft Flow.

> Read more about this role on docs.microsoft.com

Allowed to view, set and reset authentication method information for any user (admin or non-admin).

> Read more about this role on docs.microsoft.com

Manages role assignments and all access control features of Privileged Identity Management.

> Read more about this role on docs.microsoft.com

Reads usage reporting data from the reports dashboard, PowerBI adoption content pack, sign-in reports, and Microsoft Graph reporting API.

> Read more about this role on docs.microsoft.com

Full access to Microsoft Search, assigns the Search admin and Search editor roles, manages editorial content, monitors service health, and creates service requests.

> Read more about this role on docs.microsoft.com

Can only create, edit, and delete content for Microsoft Search, like bookmarks, Q&A, and locations.

> Read more about this role on docs.microsoft.com

Can read security information and reports, and manage configuration in Azure AD and Office 365.

> Read more about this role on docs.microsoft.com

Can read security information and reports in Azure AD and Office 365.

> Read more about this role on docs.microsoft.com

Creates service requests for Azure, Microsoft 365, and Office 365 services, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to SharePoint Online, manages Office 365 groups, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Full access to all Teams and Skype features, Skype user attributes, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

 

Full access to Teams & Skype admin center, manages Office 365 groups and service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Can manage calling and meetings features within the Microsoft Teams service. Assigns telephone numbers, creates and manages voice and meeting policies, and reads call analytics.

> Read more about this role on docs.microsoft.com

Reads call record details for all call participants to troubleshoot communication issues.

> Read more about this role on docs.microsoft.com

Reads user call details only for a specific user to troubleshoot communication issues.

> Read more about this role on docs.microsoft.com

The default role assigned to all users. No admin center access.

Resets user passwords, creates and manages users and groups, including filters, manages service requests, and monitors service health.

> Read more about this role on docs.microsoft.com

Not finding a perfect fit? You can create CUSTOM admin roles in Azure AD if you have Azure AD Premium Plan 1.

Assign admin roles (single or bulk) in M365 admin center

To assign admin roles to a user or multiple users via the M365 admin center:

  1. Go to the M365 admin center
  2. Select Active users from under Users
  3. Select the user(s) to whom you’re assigning an admin role and select “Manage roles” from the menu



  4. Select the role(s) to assign selected user(s) and click Save

Assign admin roles in bulk in Azure AD

To assign the same role(s) to multiple users:

  1.  Sign in to Azure AD
  2. Select Roles and administrators from the left

  3.  Select the role you want to assign



  4. Click Add assignments. Search for or find those you want to add and select each. When finished, click Add.

View/edit assigned roles in Azure AD for an individual

To review a single user’s current roles, or assign more, follow these steps:

  1. Sign in to Azure AD
  2. Find and select the user for whom you want to review admin role(s)
  3. Select “Assigned roles”

  4.  Here you’ll see current assignments and can Add or remove assignments

Getting started with Shifts in Teams

Shifts, formerly StaffHub, is an app available for use within Microsoft Teams to share schedules and allow employees to submit requests. There is also a mobile app that can be used for many of the same desktop features, and adds a clock in/out functionality for members.

In this post, I’ll go over some Shifts basics to give you an idea of what’s possible. Please note you must be a Team owner to set up Shifts for your team.

Set up Shifts for the first time

To get started using Shifts, open Teams and select the ellipses from the left menu. Here you can select the Shifts app.

Next, you’ll choose the Team for which you’re configuring Shifts. Click Create next to the correct Team.

Next, confirm the time zone you want to use for this team. Once correct, click Confirm.

Add groups/types of shifts

Next, you’ll want to do some planning. What types of Shifts are you going to be scheduling? For each “type” of shift, you’ll create a group.

Click Add group and enter a name for each group/type of shifts or roles.

Click to enlarge

Once you’ve added all the possible types of shifts, you may wish to reorder the shift groups. Just click the “move” button on any of the groups, then drag the groups into the order you wish and save.

Click to enlarge

Add members to groups/roles

Choose who will have shifts in each group by selecting the “add people to group” icon.

Add members you wish, then close the dialog.

A person can be a member of multiple roles. For example, I may have shifts in concessions AND the admin office. You must add these members manually to each group they’ll appear in – they are not automatically added to all groups.

Add shifts for members

You can double-click in any square on the schedule to add a shift.

Click to enlarge

If you add a shift to “Open shifts” you can assign it later.

Click to enlarge

Share/publish the schedule

As you add Shifts, changes will be marked with an asterisk meaning only you as an owner can see them. You must Share with team to publish changes and let others see the schedule.

Click to enlarge

After clicking Share with team, you choose which dates to “publish” and whether to notify the entire team or just those affected by the changes/additions.

Click to enlarge

Remember to “share” every time you make changes so members are aware of your edits or new schedules.

Adding time off for members

To add time off for a team member, just right-click a square in their row and choose Add time off. Time off will appear in ALL groups for that team member – you can’t have a sick or vacation day in one shift group, and work in another.

You can classify these time off shifts in different ways, and save when satisfied.

If someone requests time off after they’ve already been scheduled you can move their affected assigned shifts to open shifts to re-assign later or have them make a request for someone to cover them.

Requests

Your team members, not just owners, can make their own requests for time off, swaps, or offers (giving a shift up without taking another in return). This takes some of the administrative burden off owners and makes employees accountable for their own changes and communication.

Time off requests

Employees can request time off, which sends a request for approval to the manager/owner.

When time off has been requested, a pulsing orange dot will appear with the request on the schedule itself, not just in the Requests tab.

Swap requests

Members can offer to exchange shifts with another person. The other employee must accept the proposal for it to take affect. When making the request, your employees will be able to tell which “group” the shift is in easily.

Offer requests

Sometimes employees just want to give a shift away without taking another in return. They can “Offer” shifts to other employees which sends the other employee a request for approval (similar to swaps).

Click to enlarge

Printing schedules

Not all members may have easy access to Teams at all times, so you can easily print the schedule and post anywhere.

Using the “scale to one page” setting to make sure it will fit on your desired paper size.

Click to enlarge

Time clock

The next logical piece, after scheduling, is the actual shift performance itself. Shifts even includes a Time Clock feature which allows mobile clock-in and clock-out with optional location detection for reporting.

Click to enlarge

If you choose to turn on location tracking, you must enter the coordinates from which you expect employees to be clocking in and out.

From the Teams mobile app, team members can then clock in and clock out. They can edit clockings before confirming (in case they forgot to clock in/out) but the export/report will note the entry was edited. When clocking in/out, members will be notified if they’re noticed as off-location but can still clock in/out with that note added to the record.

The export (Shifts > Settings > Time Clock > Export) will highlight those clock-ins and clock-outs that were off-location:

Click to enlarge

Conclusion

Shifts comes at no additional cost with your O365 subscription. It could easily replace your Excel sheet schedules or expensive clock in/out software. While it may not be the best solution for all scenarios, I’d argue it could be a game-changer and cost-saver for many organizations and committees or event staff.

Creating a Team from an existing O365 group

O365 groups make up the sun in the O365 solar system. And every Microsoft Teams team (planet?) must have an O365 group to exist. You’ll also use O365 groups for group email and calendars, defining permissions for private Stream channels, collaborating with group members in Planner, and much more.

When you create a new team from scratch, it automatically creates an O365 group (and a SharePoint site collection). Whomever you invite to that team is added to the O365 group which is also used to define permissions in other apps and services throughout O365.

But let’s say you were using O365 groups before Teams was cool. You don’t want to create duplicate teams or have to manage twice as many, right? Luckily, you can use existing O365 groups to create new teams.

Create a team using an existing O365 group

In Teams, beneath your list of Teams, click Join or create a team.

Click Create team.

Choose Create from…

Next, click Office 365 group.

If you choose Team, it will copy that Team’s apps, settings, and channels to the new Team (without changing the original Team). This might be useful for classes with similar formats, or creating committees or teams based on a well-received template.

But to create a team from our existing O365 group, we’re going to select Office 365 group.

Now select the existing O365 group for which you’re establishing a new Team, then click Create.

After a moment, your new Team will be created and you’ll be taken to its landing page.

You can verify membership is correct by opening the Team’s menu (ellipses) and selecting Manage team. This

Here you can view owners, members, and guests from the O365 group. You can now modify the O365 group’s membership here if you wish, keeping in mind it affects permissions outside of Teams as well (as is the nature of O365 groups).

Microsoft recommends modifying O365 group permissions via Teams because doing so elsewhere (such as the M365 admin center) will cause a delay in taking effect and removed team members may retain Teams chat access for a couple hours.

Reorder and organize SharePoint tasks and timelines

The Tasks app (or Task list) in SharePoint has many features to help your team communicate and stay on track. You can group tasks into buckets by designating some tasks as subtasks to others. You can also specify any order of tasks, and groups of tasks, that you wish. The built-in timeline web part polishes off the task list, providing a visual representation of the tasks and phases that matter most.

Note: See bottom of post for a video demo.

Create a new subtask

To create a subtask of an existing task (thereby creating a “group”) just select the ellipses next to the parent task to open the task menu and choose “Create subtask.”


Reorder tasks

You can reorder tasks, and groups of tasks, by using the “Move up” and “Move down” buttons on the Tasks ribbon. First, select the task (or parent task of a group). Then from the Tasks menu, choose Move up or Move down.

Click to enlarge

Change an existing task to a subtask of another

If you’ve already created your tasks and need to rearrange and organize them later, you can do so.

  1. Select the task(s) you want to change into subtasks.
  2. Use Tasks > Move Up and Tasks > Move Down until the task is positioned beneath the intended “parent” task.
  3. Use “Indent” to make it a subtask of its predecessor.
Click to enlarge

To “promote” a task, you’d do the opposite, using the “Outdent” option to move a task back up a level in the hierarchy.

Create multiple layers of task groups

Using the “indent” and “outdent” options seen in the previous section, you can create many layers of tasks that can expand and collapse for a clean and organized user experience.

Add and remove tasks from the timeline

SharePoint won’t automatically add tasks to the timeline for you. This could get cluttered fast. Instead, be selective in what you choose to show on your timeline. Perhaps those that are part of the current phase or upcoming date ranges. Avoid getting too granular in what shows in this “overall” visual representation.

To add a task to the timeline, open that task’s menu and choose “Add to Timeline.”

To remove a task, simply select it on the timeline, then “Remove from Timeline

Change date range “bars” to “callouts” on the timeline

To create a cleaner appearance, you could explore changing the “bars” on your timelines to callouts. Callouts appear as brackets encompassing the date range they’re associated with and may be easier to read for some.

  1. Select the bar
  2. Click “Display as Callout” from Timeline menu
Click to enlarge

Video demonstration

The following video demonstrates a few of these tips when working with SharePoint tasks and timelines.

  • Change display format of date ranges on timelines
  • Create subtasks
  • Rearrange and reorder tasks
  • Promote/demote tasks
  • Add/remove tasks from timeline

Remove background from images using Office apps

Most of us have Office apps installed on our machine. But not everybody has, or can afford, Photoshop. Luckily, Office apps (PowerPoint, Word, Excel, and Outlook) have built-in image editing features that can do much of your basic image editing needs, including removing backgrounds from images.

I prefer to use PowerPoint, myself, but you can use whichever app you’re comfortable with or already creating within. When finished editing, you can right click the modified image to save it as an independent image file to be used elsewhere.

Note: Video demonstration at bottom of post.

Remove the background

Here are the few simple steps to start removing backgrounds from images:

  1. Select your image
  2. Picture Tools > Format will become an option once the image is selected. Click it.
  3. Select Remove Background

PowerPoint (or whichever app you’re using for this) will guess what you want to remove by highlighting it in pink.

Make corrections using the “Mark Areas to Keep” and “Mark Areas to Remove” buttons in the ribbon.

In the following animation, I’m choosing to “Mark Areas to Keep.” Once you’ve selected an option, click and drag your cursor over the pink-highlighted parts of the image you want to keep. Then select “Keep Changes” to save, removing the pink areas, and view the result.

Click to enlarge

If you’ve discovered you’ve made a mistake or want to keep or remove additional areas in the image, you can select the image again, choose “Remove Background” and continue the steps above until the result is to your liking. If you decide you want to restore the background and undo your changes, you can choose “Discard All Changes.”

Save the image(s)

When finished, you can save the modified image (which will preserve the transparent background) by right-clicking your result and choosing “Save as Picture.”

If you want to combine multiple edited images into a single image file, as in the following example, group the images first.

  1. Select an image
  2. Select all (Ctrl+A) to select all images
  3. Right click a selected image > Group > Group
  4. Right click the new group > Save as Picture

Video demo

Here’s a video showing this process on multiple image types, layering those images into a final result.

Workaround: Export to Excel not working in Safari, Chrome, or other non-IE browsers for SharePoint server list exports

The problem

If you’re using SharePoint online (O365) you won’t run into this issue. But for those of you, like me, who are still on SharePoint 2013 or 2016 you may have tried to use “Export to Excel” on a SharePoint list or library and received the following error message:

To export a list, you must have a Microsoft SharePoint Foundation-compatible application.

Clicking “OK” only redirects you to page that is most definitely not an Excel export:

The workaround

Using Internet Explorer to open and export your SharePoint list might be the simplest way to export your list. However, some are unable to use IE.

If you can’t download or use Internet Explorer (IE) you can still get your lists exported to Excel by working in reverse. Rather than export from SharePoint we are going to open Excel and import from SharePoint.

1. Open a blank workbook in Excel

2. Navigate to the Data ribbon –> Get Data > From Other Sources > From SharePoint List

Note: There are two SharePoint list options under Get Data. Do not select From Online Services > From SharePoint Online list (this option would be for O365 only and O365 users will not need this post’s workaround).

3. Paste the URL to your SharePoint site. You can paste the URL to the list you’re wanting to import, but delete everything after the site’s name in the URL.

4. Click OK.

5. Select Windows and leave credentials as the default “Use my current credentials” unless you have reason to access the list as another account (perhaps a service account which may be able to retrieve all items even with restricted item-level permissions).

6. Click Connect.

7. Find the name of the list you want to import, select it, and click Load.

Click to enlarge

8. Depending on the size of your list, this may take a while. Once imported, you’ll find all of your data as well as some SharePoint metadata associated with your list items.

Click to enlarge

While not ideal, it doesn’t take long to do and you do get the result you’re looking for (with a bit of cleanup, deleting unnecessary columns and such).

Some things to keep in mind:

  • Exporting from SharePoint using IE allows you to export a view.
  • Importing from SharePoint using Excel imports every single list item and column, regardless of view.
  • You’re only able to import items to which you at least have view permissions.

On-premises data gateway for Power BI, Power Automate (Flow), and Power Apps

On-premises data gateways allow users to connect online services like Power BI service, Power Automate (formerly Microsoft Flow), and Power Apps to their on-premises data sources such as SharePoint Server, SQL databases, and network file shares.

The gateway has allowed me to use modern services like Power Automate to bring advanced functionality to my SharePoint 2013 and 2016 lists and libraries, such as copying items across site collections (not possible in SharePoint Designer). I’ve also been able to share Power BI data visualizations and reports of limited on-prem data sources such as SQL databases with off-prem consumers who wouldn’t otherwise have access to that data.

One data gateway will cover all three apps – you don’t need separate gateways for each app/service. You can, however, have two gateways per machine but a max of one per mode:

  • Regular mode (share access to data)
    • Multiple users
    • All services (BI, Automate, Apps)
    • Supports Power BI scheduled refresh and live query
  • Personal mode (others don’t need access to data)
    • One user
    • Only works with Power BI
    • Only supports Power BI scheduled refresh

Install a gateway

The following steps are adapted from documentation available on Microsoft Docs. Please read all available information (see “More info” section at bottom of post) prior to installing a gateway to make sure you’re configuring it correctly for your organization’s needs.

  1. Download and install the gateway
    • Install on a server/computer that is always on to make sure Flows and refreshes aren’t disrupted due to the gateway being unavailable on a powered down machine. You might consider installing multiple gateways and creating a gateway cluster for high availability or load balancing in case a server goes down.
    • Install on a personal/work computer if it will only be used by one person or machine manually (perhaps for those building Power BI reports without automatic refreshes). This is not ideal for scheduled flows or data refreshes as the machine may be powered down.
    • Note: You can restrict who is allowed to install on-premises data gateways for your organization.
  2. Configure the gateway.
  3. Add gateway admins
    • By default, the individual/account that installs a gateway is a gateway admin. You can manage admins, adding other security groups or individuals as needed.
  4. Use the gateway to refresh an on-premises data source such as a SQL database (specific to Power BI).
  5. Troubleshoot gateways

Share a data gateway

To manage gateways from your apps, follow these paths;

  • Power Automate –> Settings wheel –> Gateways
  • Power Apps –> Data –> Gateways
  • Power BI –> Settings wheel –> Manage gateways

In Power Automate and Power Apps, your options are identical from here. You can view additional information on existing gateways, download another gateway, and share or delete existing gateways.

If you share gateways with others, you get a dialog with the option to include everyone in the organization. This way anyone could have access to the gateway to use in their own Flows, apps, and reports. And unless a user is an admin, they can’t reconfigure the gateway :

When you share a gateway with others, you can provide these levels of access within Power Automate and Power Apps (not Power BI):

  • Can use (for those just using apps)
  • Can use + share (for those sharing apps, which will automatically share the gateway)

More info on these levels available here.

When assigning permissions, you can specifically allow these permissions:

In all three apps, you can make someone else (or a security group) an admin. If you make another user an admin of a gateway, they have the same rights as you to manage the gateway and its configuration in its entirety.

More information from Microsoft Docs