The internal name of a SharePoint column is a unique name that is automatically generated by SharePoint when a column is created. It is used by SharePoint internally to reference and retrieve the value of a particular column associated with an item or document. The internal name is generated based on the display name you provide but all special characters and spaces will be replaced with Unicode’s by SharePoint. Internal name is generated only once while creating a new column and it cannot be changed even if you change the display name of SharePoint column.

The internal name is not visible to users in the SharePoint user interface by default, but it is commonly used in various scenarios, such as in SharePoint REST APIs, Power Automate flow expressions, Power Apps formulas, PowerShell, etc. to interact with column data programmatically.

Where are Internal names of SharePoint columns used?

1. Custom Scripts: When creating custom scripts, such as JavaScript or PowerShell, the internal names of columns are required to reference and manipulate the values of the columns while interacting with SharePoint data.
2. Workflows: In SharePoint Designer workflows or Microsoft Power Automate (formerly known as Microsoft Flow), the internal names of columns are used to reference the values of the columns as inputs or outputs in the workflow actions and in expressions.
3. Custom Solutions: When building custom solutions, such as SharePoint apps, SharePoint framework (SPFx) web parts, or custom code, the internal names of columns are required to interact with the columns programmatically.
4. Power Apps: Few of the Power Apps functions like ShowColumns, SortByColumns, etc. requires using internal names of SharePoint columns in formula.
5. JSON Formatting: Internal name of SharePoint column is required in JSON formatting to reference the column value with [$InternalNameOfColumn] syntax.

How to find the Internal name of a SharePoint column?

Using Modern experience list view

You can use sorting or filtering options from SharePoint online modern experience list view to find the internal name of a SharePoint column. Sort by and Filter by options are supported by most of the column types in SharePoint like Single line of text, Choice, Number, Date and Time, Yes/No (Boolean), Person or Group (single selection), etc.

For this afticle, we will use sorting based on SharePoint choice column as an example:

1. Go to the SharePoint online list for which you want to check the internal name of a column.

2. Click on column name/header from the list view and select either Ascending (A to Z) or Descending (Z to A) option from the popup menu:

3. SharePoint will sort the list view based on selection and the browser URL will be changed like:

https://contoso.sharepoint.com/sites/wlive/Lists/InternalNames/AllItems.aspx?sortField=ChoiceColumn&isAscending=false

Where column name (ChoiceColumn) after sortField= is the internal name of your SharePoint choice column.

4. Similarly, when you use Filter by option in SharePoint modern experience to filter the list view based on Date and Time column (named as Start Date), SharePoint changes browser URL like:

https://contoso.sharepoint.com/sites/wlive/Lists/InternalNames/AllItems.aspx?FilterField1=Start_x0020_Date&FilterValue1=2023-04-05&FilterType1=DateTime

Where column name (Start_x0020_Date) after FilterField1= is the internal name of your SharePoint date and time column. Notice _x0020_ in internal column name which is an Unicode encoding of the space character in the display name of date and time column (Start Date).

Using Classic experience List settings page

Few of the SharePoint column types like Multiple lines of text, Hyperlink or Picture, Image, etc. does not support sorting or filtering from SharePoint modern experience list views. So, you have to use the classic experience list settings page to find the internal name for such SharePoint columns.

Follow below steps to find the internal name of multiple lines of text column using SharePoint classic experience list settings page:

1. Go to the SharePoint online list for which you want to check the internal name of a column.

2. Click on Settings (gear) icon from the top right corner and select List settings:

3. From list settings page, scroll down to the Columns section and click on the column name for which you want to find the internal name:

4. SharePoint will open column settings page for the respective column with browser URL like:

https://contoso.sharepoint.com/sites/wlive/_layouts/15/FldEdit.aspx?List=%7B6FBA7FAE-AFC0-45D6-99EE-0AB20629EE41%7D&Field=MultilineTextCol

Where column name (MultilineTextCol) after Field= is the internal name of your SharePoint online multiple lines of text column.

Note: You can use this classic experience method to find out the internal name of SharePoint columns for all column types.

Using SharePoint REST API

You can use SharePoint REST API endpoint like below to get the internal name of SharePoint column based on it’s display name. Open URL in below format directly from browser tab:

https://contoso.sharepoint.com/sites/SPConnect/_api/web/lists/getbytitle('InternalNames')/fields?$select=Title,InternalName&$filter=Title eq 'Multiline Text Column'

Using PnP PowerShell

You can use below PnP PowerShell script to find the internal name of SharePoint online list column using PnP PowerShell:

# SharePoint online site URL
$siteUrl = "https://contoso.sharepoint.com/sites/wlive"

# Display name of SharePoint list
$listName = "InternalNames"

# Display name of SharePoint list column
$columnName = "Multiline Text Column"
 
# Connect to SharePoint online site
Connect-PnPOnline -Url $siteUrl -Interactive
 
# Get internal name of SharePoint list column
Get-PnPField -Identity $columnName -List $listName | Select Title,InternalName

Using CLI for Microsoft 365

You can use below CLI for Microsoft 365 script to find the internal name of SharePoint online list column using CLI for Microsoft 365:

# SharePoint online site URL
$siteUrl = "https://contoso.sharepoint.com/sites/wlive"

# Display name of SharePoint list
$listName = "InternalNames"

# Display name of SharePoint list column
$columnName = "Multiline Text Column"
 
# Get Credentials to connect
$m365Status = m365 status
if ($m365Status -match "Logged Out") {
	m365 login
}

# Get internal name of SharePoint list column
m365 spo field get --webUrl $siteUrl --listTitle $listName --title $columnName --output text

Best practices for naming SharePoint columns

When creating columns in SharePoint, it’s important to follow best practices for column naming to avoid using special characters or Unicode characters in internal names. Here are some recommended best practices:

1. Use only alphanumeric characters: Stick to using letters (A-Z, a-z) and numbers (0-9) in column names. Avoid using special characters such as @, #, $, %, _, etc. Avoid column names beginning with numbers.
2. Avoid spaces: Use PascalCase to separate words in column names instead of spaces. For example, use ColumnName instead of Column Name. This can help prevent issues with URLs, Unicode encoding, and referencing column names in scripts or code.
3. Avoid reserved words: SharePoint has reserved words that are used for system functionality, and using them in column names may cause conflicts. Examples of reserved words include “ID”, “Modified”, “Created”, “Title”, etc. Avoid using these reserved words as column names.
4. Keep it concise and meaningful: Use descriptive and meaningful names for columns that clearly indicate their purpose. Avoid using vague or generic names that may be confusing or ambiguous to users. Use column description to provide more information about the columns.
5. Be consistent: Establish a consistent naming convention for columns across your SharePoint site or site collection to ensure uniformity and ease of management. This can also help with documentation, training, and maintenance of your SharePoint environment.

I hope you liked this article. Give your valuable feedback & suggestions in the comments section below and share this article with others.

Learn more

• SharePoint Online: Apply JSON View formatting using SharePoint REST API
• Change SharePoint Online List URL using PnP PowerShell
• SharePoint Online: Download files using JSON Formatting
• Create a SharePoint online site using Power Automate flow

A while back, I published a post on some most famous and important limits we have in SharePoint Online. These were limits that are most likely to be encountered by the users. Today, I want to introduce you to another limit that exists in SharePoint. Most of you will never experience it; however, for those who use lists or metadata on document libraries – you better not skip this article and read it to the end. Otherwise, you will join a very disappointed percentage of my blog readers after encountering the Lookup Column Threshold limit on a view and then desperately finding this post by googling for a solution. 😊

What is the Lookup Column Threshold limit on a view?

When you create lookup columns on a list or library, for performance reasons, Microsoft limits you to 12 (twelve) columns of that type of column in a single view. This is because when it is a lookup column, you are getting the data from either another list or another source. When you exceed the limit, you will get the following error message when trying to add the 13^th column to the view (this just proves that 13 is not a lucky number).

Something went wrong. The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.

And when you refresh the page and try to go to the document library, your library will go blank with the error message front and center.

What are lookup columns?

What is interesting here is that the lookup column in the content of this error message is not the same lookup column I blogged about some time ago.  In that post, I referred to the Lookup column type that we have in Lists and Libraries. Those Lookup columns were columns that referenced other lists and libraries on the same site.

In the context of the Lookup Column Threshold limitation, Lookup Column also refers to other types of columns! By lookup columns here, we imply the columns that obtain their data from other sources. Here is a complete list of the “lookup” columns that will cause the error message:

System Columns (Columns created and displayed out of the box)

• Created By Column (lookup against User/Employee Directory)
• Modified By Column (lookup against User/Employee Directory)
• Type Colum (Word, Excel, PowerPoint, PDF, etc.)
• Name Column (Filename)

Manual Columns (Columns created manually by users)

• Lookup Column (the actual Lookup column, lookup against another list or library)

• People Column (lookup against User/Employee Directory)
• Managed Metadata Column (lookup against the Term Store)
• Enterprise Keywords Column (lookup against the Term Store)

How to avoid the Lookup Threshold limit on a view

• The only way to avoid this issue is to limit your views to less than 12 lookup column types mentioned above. To be clear, you can have as many columns as you wish on a list or library, as long you do not exceed the 12 “lookup” ones in a single view. So, if you encounter the above limit, just hide some of the “lookup” columns from a view to fix it.
• Where appropriate, use other types of columns instead of the lookup columns (i.e., Choice instead of Managed Metadata) since those do not count against the limit
• If you do have many columns you need to display – create additional views on a list or library – just keep the number of “lookup” columns under 12 in any given view.

The post How to avoid Lookup Column Threshold limit on a view appeared first on SharePoint Maven.

The internal name of a SharePoint column is a unique name that is automatically generated by SharePoint when a column is created. It is used by SharePoint internally to reference and retrieve the value of a particular column associated with an item or document. The internal name is generated based on the display name you provide but all special characters and spaces will be replaced with Unicode’s by SharePoint. Internal name is generated only once while creating a new column and it cannot be changed even if you change the display name of SharePoint column.

The internal name is not visible to users in the SharePoint user interface by default, but it is commonly used in various scenarios, such as in SharePoint REST APIs, Power Automate flow expressions, Power Apps formulas, PowerShell, etc. to interact with column data programmatically.

Where are Internal names of SharePoint columns used?

1. Custom Scripts: When creating custom scripts, such as JavaScript or PowerShell, the internal names of columns are required to reference and manipulate the values of the columns while interacting with SharePoint data.
2. Workflows: In SharePoint Designer workflows or Microsoft Power Automate (formerly known as Microsoft Flow), the internal names of columns are used to reference the values of the columns as inputs or outputs in the workflow actions and in expressions.
3. Custom Solutions: When building custom solutions, such as SharePoint apps, SharePoint framework (SPFx) web parts, or custom code, the internal names of columns are required to interact with the columns programmatically.
4. Power Apps: Few of the Power Apps functions like ShowColumns, SortByColumns, etc. requires using internal names of SharePoint columns in formula.
5. JSON Formatting: Internal name of SharePoint column is required in JSON formatting to reference the column value with [$InternalNameOfColumn] syntax.

How to find the Internal name of a SharePoint column?

Using Modern experience list view

You can use sorting or filtering options from SharePoint online modern experience list view to find the internal name of a SharePoint column. Sort by and Filter by options are supported by most of the column types in SharePoint like Single line of text, Choice, Number, Date and Time, Yes/No (Boolean), Person or Group (single selection), etc.

For this afticle, we will use sorting based on SharePoint choice column as an example:

1. Go to the SharePoint online list for which you want to check the internal name of a column.

2. Click on column name/header from the list view and select either Ascending (A to Z) or Descending (Z to A) option from the popup menu:

3. SharePoint will sort the list view based on selection and the browser URL will be changed like:

https://contoso.sharepoint.com/sites/wlive/Lists/InternalNames/AllItems.aspx?sortField=ChoiceColumn&isAscending=false

Where column name (ChoiceColumn) after sortField= is the internal name of your SharePoint choice column.

4. Similarly, when you use Filter by option in SharePoint modern experience to filter the list view based on Date and Time column (named as Start Date), SharePoint changes browser URL like:

https://contoso.sharepoint.com/sites/wlive/Lists/InternalNames/AllItems.aspx?FilterField1=Start_x0020_Date&FilterValue1=2023-04-05&FilterType1=DateTime

Where column name (Start_x0020_Date) after FilterField1= is the internal name of your SharePoint date and time column. Notice _x0020_ in internal column name which is an Unicode encoding of the space character in the display name of date and time column (Start Date).

Using Classic experience List settings page

Few of the SharePoint column types like Multiple lines of text, Hyperlink or Picture, Image, etc. does not support sorting or filtering from SharePoint modern experience list views. So, you have to use the classic experience list settings page to find the internal name for such SharePoint columns.

Follow below steps to find the internal name of multiple lines of text column using SharePoint classic experience list settings page:

1. Go to the SharePoint online list for which you want to check the internal name of a column.

2. Click on Settings (gear) icon from the top right corner and select List settings:

3. From list settings page, scroll down to the Columns section and click on the column name for which you want to find the internal name:

4. SharePoint will open column settings page for the respective column with browser URL like:

https://contoso.sharepoint.com/sites/wlive/_layouts/15/FldEdit.aspx?List=%7B6FBA7FAE-AFC0-45D6-99EE-0AB20629EE41%7D&Field=MultilineTextCol

Where column name (MultilineTextCol) after Field= is the internal name of your SharePoint online multiple lines of text column.

Note: You can use this classic experience method to find out the internal name of SharePoint columns for all column types.

Using SharePoint REST API

You can use SharePoint REST API endpoint like below to get the internal name of SharePoint column based on it’s display name. Open URL in below format directly from browser tab:

https://contoso.sharepoint.com/sites/SPConnect/_api/web/lists/getbytitle('InternalNames')/fields?$select=Title,InternalName&$filter=Title eq 'Multiline Text Column'

Using PnP PowerShell

You can use below PnP PowerShell script to find the internal name of SharePoint online list column using PnP PowerShell:

# SharePoint online site URL
$siteUrl = "https://contoso.sharepoint.com/sites/wlive"

# Display name of SharePoint list
$listName = "InternalNames"

# Display name of SharePoint list column
$columnName = "Multiline Text Column"
 
# Connect to SharePoint online site
Connect-PnPOnline -Url $siteUrl -Interactive
 
# Get internal name of SharePoint list column
Get-PnPField -Identity $columnName -List $listName | Select Title,InternalName

Using CLI for Microsoft 365

You can use below CLI for Microsoft 365 script to find the internal name of SharePoint online list column using CLI for Microsoft 365:

# SharePoint online site URL
$siteUrl = "https://contoso.sharepoint.com/sites/wlive"

# Display name of SharePoint list
$listName = "InternalNames"

# Display name of SharePoint list column
$columnName = "Multiline Text Column"
 
# Get Credentials to connect
$m365Status = m365 status
if ($m365Status -match "Logged Out") {
	m365 login
}

# Get internal name of SharePoint list column
m365 spo field get --webUrl $siteUrl --listTitle $listName --title $columnName --output text

Best practices for naming SharePoint columns

When creating columns in SharePoint, it’s important to follow best practices for column naming to avoid using special characters or Unicode characters in internal names. Here are some recommended best practices:

1. Use only alphanumeric characters: Stick to using letters (A-Z, a-z) and numbers (0-9) in column names. Avoid using special characters such as @, #, $, %, _, etc. Avoid column names beginning with numbers.
2. Avoid spaces: Use PascalCase to separate words in column names instead of spaces. For example, use ColumnName instead of Column Name. This can help prevent issues with URLs, Unicode encoding, and referencing column names in scripts or code.
3. Avoid reserved words: SharePoint has reserved words that are used for system functionality, and using them in column names may cause conflicts. Examples of reserved words include “ID”, “Modified”, “Created”, “Title”, etc. Avoid using these reserved words as column names.
4. Keep it concise and meaningful: Use descriptive and meaningful names for columns that clearly indicate their purpose. Avoid using vague or generic names that may be confusing or ambiguous to users. Use column description to provide more information about the columns.
5. Be consistent: Establish a consistent naming convention for columns across your SharePoint site or site collection to ensure uniformity and ease of management. This can also help with documentation, training, and maintenance of your SharePoint environment.

I hope you liked this article. Give your valuable feedback & suggestions in the comments section below and share this article with others.

Learn more

• SharePoint Online: Apply JSON View formatting using SharePoint REST API
• Change SharePoint Online List URL using PnP PowerShell
• SharePoint Online: Download files using JSON Formatting
• Create a SharePoint online site using Power Automate flow

As I work with my clients, assisting them with the build-out of the Intranet portal, a persistent question/request I get is what is the best way to organize company templates in SharePoint Online. So in this article, I decided to list a few options available.

I wrote a similar post back in 2020 where I shared a total of 5 ways to store templates. However, that post was more of a way to store department templates and those used by a limited group of users. In contrast, in this article, I am listing options for company-wide, global templates to be used by the whole organization.

Option 1: Read-only Document Library

The first option is pretty standard and most frequently used by many organizations. The idea is to create a read-only document library and have it prominently displayed/accessed via your SharePoint Intranet. You can also get creative and use metadata to organize the library as well.

Pros

• Easy to implement

Cons

• Users have to manually download, complete, and save the template in a separate, designated area

Option 2: New drop-down on a library

Another option is to utilize Add Template option available on the modern document library. This option allows users to navigate to a designated library, click the New button and complete the template “on the fly.” This will automatically save the filled-out document in a library as well. I described this option in great detail here (Option 5 in that article).

Pros

• No need for the user to save a template locally, rename and re-upload it to SharePoint

Cons

• It always opens the template in the browser mode, not the native application, which might limit some capabilities
• It might not be “evident” to the users to click on the New button

Option 3: Organization Assets Library

The other option available to organize company templates in SharePoint Online is to use PowerShell. This option essentially designates a certain document library in SharePoint as the formal library of templates and makes it available to use from the Office applications as well SharePoint itself when you browse for images/photos.

You can find the detailed instructions on how to set this up here.

Pros

• Templates are easily accessible from the Office applications

Cons

• Requires PowerShell + SharePoint Administrators to set up

The post How to organize company templates in SharePoint Online appeared first on SharePoint Maven.

As a SharePoint consultant, I work with hundreds of small to medium-sized businesses, assisting them with the transition to SharePoint Online and Microsoft 365. When they initially establish contact with me, they might share some basic business requirements, most of which are not an issue. However, once in a while, as I read through or we discuss requirements, I see or hear the following: “Our CEO wants to ensure that SharePoint will not look like SharePoint” WHAT??? 😕 In case you encounter the same sentiment in your organization, let me explain why this requirement does not make sense and should be removed from the list.

It never made sense to brand SharePoint

The first time I blogged about the topic was back in 2016. You can check this post here. This was back when we still had SharePoint classic, and SharePoint did not look like SharePoint today. This was also before Teams and many other applications we now have available within Microsoft 365. In that 2016 post, I did provide a few legitimate reasons why heavily branding SharePoint did not make much sense. I will be reiterating some of those reasons in this article as well.

Modern SharePoint is already beautiful!

Fast forward six years from that old post of mine, and modern SharePoint is nothing like it used to be. It is almost like two different applications. We now have a set of new site types (Team Site and Communication Site), built-in site templates to make your sites pretty, + a number of options to change the look and feel of your SharePoint sites (you can tweak navigation, headers, logos, apply built-in and custom themes, etc.).

Example of the ability to adjust the SharePoint navigation experience

Example of the ability to adjust SharePoint site logo

Examples of various themes/color palettes available in SharePoint out of the box

Built-in SharePoint Site Templates

Additional site templates available in SharePoint Lookbook

I think that most people who ask for SharePoint not to look like SharePoint simply are not aware of what modern SharePoint looks like and might have a bad taste in their mouth from their previous experience with classic SharePoint. As such, I recommend doing a SharePoint demo first to explain what modern SharePoint is all about, how it looks, and so on.

SharePoint Branding options

If you want to apply custom branding to SharePoint, you have several options available out of the box. I recently wrote a post that outlines all the available options.

The abovementioned techniques will help you make SharePoint prettier without hiring custom developers and designers.

Impact of trying to make SharePoint not look like SharePoint

If you have been following my posts for a while, you know that I am a huge advocate of an Out of the Box approach regarding SharePoint configuration and development. Most of the reasons that would apply to my OOTB business philosophy also apply to this article. I already listed a few back in that 2016 post. However, due to so many changes that have happened over the years, I would like to repeat them here again + add a few new ones to help get my point across on why you should stop making SharePoint not look like SharePoint.

1. Cost

This is an obvious one. Be ready to spend significant money if you want to deviate from the OOTB SharePoint design. You need to hire a designer and a developer or purchase third-party themes. No matter which route you take, it will cost you additional money on top of the Microsoft 365 licenses you already pay for.

2. Timeline

Another obvious factor missed by many. Your implementation timeline will be significantly longer when you engage in custom designs and themes. With OOTB SharePoint, you just configure the sites/pages and are ready to go live. With custom themes and development, you would need to spend extra time on the implementation, plus testing, to make sure custom coding/theme did not come into conflict with other features/customizations you might have.

3. Flat Information Architecture

When Microsoft modernized SharePoint back in 2017, the most significant change besides look and feel was the introduction of flat architecture. Instead of subsites, we now have modern sites and use Hubs to connect them all together. That change also made custom branding harder to implement. In the old days, you would create a custom theme/design, apply it to the site collection, and that theme would apply/propagate to all the subsites underneath. In the modern world, we do not use subsites anymore. We have sites that are independent of one another. So that means applying branding custom theme/design to one site does not propagate to another site. So that means it is unlikely you will have all your sites branded the same way in your tenant.

4. Lack of Additional Expertise

The other thing you need to worry about is the fact that you would need to maintain SMEs on staff who are familiar with the custom theme you purchased/acquired. And if you venture into custom development – you are going to be “married” to that developer for the whole time. So that means extra spending for every little change you want to make, not to mention you would need to assure the staff/IT you hire would have the ability to customize/get familiar with custom changes you made.

5. Teams Impact

With the introduction of Teams in 2017, we now have a completely new way of how users collaborate. In the past, users navigated SharePoint sites and used Outlook for email communications. Nowadays, everyone lives in the Teams application, accessing SharePoint using the teams interface. So they no longer navigate the SharePoint sites, but rather access files using the File tab in a given Teams channel. And Teams does not allow for any custom branding. On top of that, whatever you brand your SharePoint site does not really matter, because users won’t even see it when they click on the Files Tab.

Example of the Files Tab in Teams which shows the contents of a SharePoint Document Library folder

The only impact of custom branding you might get is if you set up Viva Connections which will allow embedding of your Intranet into the Teams App.

Viva Connections (embedding of SharePoint Intranet into Teams Application)

6. OneDrive Sync impact

Similar to the above, if your users are only after documents, they will most likely sync given libraries locally to Windows Explorer and will never visit your beautiful SharePoint site again. So whatever custom you have in terms of theme and look and feel, won’t matter much since users will not see it much.

Example of SharePoint and OneDrive files and folders synchronized using OneDrive Sync Client

7. Mobile Applications

Another important factor to consider is the fact that some users might access SharePoint sites via the SharePoint mobile application. If you have custom development, it might not be compatible or even render on a mobile device, so definitely something else to consider.

Example of a SharePoint Mobile Application which can’t be customized

8. SharePoint Online changes

One other significant thing you must take into account is the fact that you have no control over SharePoint Online changes happening literally ALL THE TIME. Previously, you would configure SharePoint on your servers and would not need to worry about changes until the next significant upgrade (i.e., going from SharePoint 2010 to SharePoint 2013). Not anymore. With SharePoint Online, you do not have control of the application – since it is in the cloud. Microsoft does. So you want to ensure that whatever customizations you make will not come into conflict with some new features or updates Microsoft rolls out.

One noteworthy example was the roll-out of the SharePoint App Bar back in 2021. It worked seamlessly for organizations that stuck to an OOTB configuration. However, for some who did custom development, this turned into a nightmare. The SharePoint App bar, which always appears on the left side of all SharePoint sites, ran into conflicts with the custom themes and custom navigation built for certain custom themes. As a matter of fact, and as a result of the above, Microsoft allows for temporary disablement of the SharePoint App Bar.

Message about SharePoint App Bar within the Microsoft 365 Message Center

9. Other applications within Microsoft 365

Finally, I want to mention that SharePoint is just one application in the sea of applications we now have in Microsoft 365. We also have OneDrive, Planner, Teams, Lists, and many others, and none of them allow for any branding or customizations beyond what you are getting out of the box. So it is yet another reason to stop making SharePoint not to look like SharePoint. I yet have to hear someone complain about the lack of branding capabilities within those applications.

The post Stop asking for SharePoint not to look like SharePoint appeared first on SharePoint Maven.

One of the primary goals of the Intranet portals is, of course, to share the news and announcements within the organization. SharePoint has some fantastic options available in this area, thanks to the mighty News Web Part. I blogged quite a bit about the News Web Part and various capabilities we have (i.e., the ability to schedule or expire news posts, etc. Today, though, I want to share some advice on a few ways to organize department news on your SharePoint Hub, especially if you are part of a larger organization.

Use Case

Most small organizations have just one area for news and announcements on the main landing page (Home Site). And most likely, you have just one person managing those announcements. However, if you are part of a larger organization, you probably have different categories of announcements managed and curated by different employees and departments. So the advice below is really for this situation described above.

Option 1: Organize department news via Metadata Categories

The first option would be to utilize multiple News Web Parts on the same site and filter them based on the metadata tags assigned to each news post. The idea is that you will create a Metadata Column called Category or Department on the Site Pages Library on the main Intranet Hub Site and tag each news post against that respective category (i.e., HR News, IT News, General News). Then, on the main landing page itself, you will add several news web parts, one for each category, and then filter the News Web Parts based on the corresponding page property (i.e., Category = HR News). If what I described above sounds a bit complicated, do not worry; I explained how to set this up in great detail in this post.

Tagging a News Post against a predefined category

Filtering the News Web Part for a specific Category (Page Property)

Pros:

• All the news posts reside in one site and one library (Site Pages Library), making it easy to maintain (i.e., add/edit/delete/schedule/expire)
• This option is great if you have different categories of news to publish but have one person to maintain them all

Cons:

• In the case of multiple authors, no way to split security since all news posts reside in one site/library (need to give all authors Edit access to the main Hub Site).

Option 2: Organize department news via separate sites on Hub

The other option to organize and publish the news on an Intranet is to utilize the Hub functionality of SharePoint. The idea is that each area/category of news gets its own site to create news and announcements, and then those news posts are rolled up on a main Home Site/Intranet Site thanks to the Hub news roll-up feature.

Pros:

• Allows each department/category own space to create news posts – separated with own security/permissions. For example, you could create a separate site called “CEO Corner” and have the CEO publish the posts on that site, which will be rolled up and presented in the specific News Web Part on a SharePoint page which will only filter for news posts from that CEO Corner site.

Cons:

• No way to pick and choose the specific news posts to publish from rolled-up sites – it is all or nothing
• In case certain news are to be expired on a Hub site after a certain period of time – the setup would require the use of managed properties (since each and every site will have its own Expiry date column)
• Less control for the overall aggregation of the news posts

The post 2 ways to organize department news on your SharePoint Hub appeared first on SharePoint Maven.

In the past, I blogged about various technical capabilities of SharePoint Intranet and the vast possibilities when it comes to design and look and feel options. However, today, I want to explain two different ways to approach Intranet development/creation and discuss a few options regarding implementation strategy/philosophy.

There are two ways you can approach the SharePoint Intranet, and they differ in terms of timeline and involvement.

Option 1: Minimal Viable Product (MVP)

The first option to create a SharePoint Intranet is to create a Minimal Viable Product (MVP) and take it live as soon as possible. The idea behind this option is that you initially create something relatively simple in terms of design and content and then make gradual changes and improvements over time, often based on users’ feedback.

This option does not imply that you do not gather some initial requirements and content from the business users. It just means that you will spend less time doing so. The goal here is to develop something useful and functioning quickly so that you can realize ROI immediately and have a common place for employees to go to and access company-wide information.

To achieve this option, you can build from scratch or use one of the built-in templates. I listed a few options and shared some sample designs available in this article.

Example of an Intranet built from scratch using Out-of-the-Box functionality

Example of the Site Template selection available in SharePoint

The main driver behind this option is the fact that Intranet is a project without an end date – it is constantly evolving, and the design and content will change accordingly as time goes by. It also hinges on the fact that with modern SharePoint, you can easily adjust the design and architecture on the fly. For example, if you want to completely change your Intranet site’s look and feel, you can do so easily with page editing and a vast template selection. If you would look to re-reorganize navigation – once again – this is an easy task. Likewise, you can easily reorganize your hub architecture, and rename the site and URL on the fly.

So the idea here is to release something to the employees really quickly, even if it contains some minimal information. In other words, it grows organically and matures as time goes by. As long as the site is secure, you can promote your Intranet to your employees and then make further changes and improvements on the fly.

Pros

• Quick Timeline
• Minimal cost (if hiring an external resource to create one)
• A better option if you want to grow Intranet organically and gather feedback from employees

Cons

• It might not contain all the content
• It might not contain all the wishlist items or advanced capabilities

Option 2: Customized Intranet

As you probably already guessed, the second option is the opposite of Option 1. The idea here is that you go through the detailed business requirements upfront with your users/stakeholders and customize the intranet according to those requirements. The assumption here is that you go through many iterations/design changes to address the requirements, and the project is not completed until most of these requirements are met. Quite often, companies also opt for additional customizations and integrations often not available via the out-of-the-box functionality.

The Intranet development might often rely on some of the functionalities and business processes that might rely on automation (Power Automate) and custom apps (Power Apps).

Pros

• More complete than MVP (contains additional functionality)

Cons

• Much Longer Timeline
• More expensive (if hiring external resources and relying on 3^rd party themes and further customizations)

Verdict

You probably already guessed from the tone of this post that my preferred option is Option 1 – build a Minimal Viable Product (MVP) right away and grow it organically within the organization as the company matures in terms of technology and content. When working with clients, I do support both options, but I always advocate for a quick turnaround/timeline. In my opinion, this option provides lots of flexibility and gives power to Site Owners to adjust the look and feel as necessary.

I typically find this Option popular among smaller organizations, that do not have large budgets to spend on the Intranet. Larger/Global organizations, who might have specific communication, compliance, and country-specific legal requirements, and obligations, might often prefer the second option.

The post 2 ways to create SharePoint Intranet appeared first on SharePoint Maven.

The situation I am about to describe in this article is inevitable. A user creates a Team in Teams or a Team Site in SharePoint. This, in turn, makes that user an Owner of that Team/Microsoft 365 Group. At some point, the user leaves the organization, and their account is deleted from Microsoft 365. The result is the Team/Team Site/Microsoft 365 Group that no longer has any owners. Some time ago, I published an article where I advocated keeping the number of owners on a site to a minimum, but what can we do when the number of owners becomes zero? Luckily, there is a way to handle those situations in Microsoft 365, which is what I want to explain in this post.

Owners and Members

The Microsoft 365 Group contains owners and members. Members can add/edit/delete content, and owners can do all of the above + also invite other members, delete the group or tweak its many settings.

Ownerless Groups

As mentioned above, people come and go, and the following situation can occur – the Owner’s account is deleted or disabled in Microsoft 365 (if the user is no longer with the organization). This leaves the other members on a site/group without “the boss.” It does not automatically stop collaboration, but should certain site, team, or group settings be tweaked, this won’t be possible.

It is important to note that the team/team site/Microsoft 365 Group can only become “ownerless” only when the Owner’s account is deleted or disabled in Microsoft 365 Admin Center. Suppose the Owner decides to leave a given Microsoft 365 Group or demote to the Member level. In that case, the system will not allow for this to occur unless someone else is manually promoted to the Owner role.

Manually add owners to the Microsoft 365 Group

If a group became Ownerless, your SharePoint or Microsoft 365 Global Admin would be able to manually add owners to the group via the SharePoint Admin Center – I described this mechanism in an earlier post.

Configure Microsoft 365 Group settings to avoid Ownerless Teams and Microsoft 365 Groups

However, another option is available that allows you to automate the process. The idea is that you can configure the settings such that when the group becomes ownerless, its members are immediately sent an email asking them to step up and become the Owner of the Group. Here is how to achieve this. You need to be a Global Microsoft 365 Admin to be able to set this up.

1. Click on Microsoft 365 App Launcher, then Admin
2. Under Settings, click Org settings
3. Under the Services tab, scroll down and click Microsoft 365 Groups
4. On the pop-up that appears, you will need to check the box next to “When there’s no owner, email and ask active group members to become an owner.”
5. If you would like to accept default settings, all you need to do is click Save. However, if you would like to configure notifications, email message, and other settings, you would need to click Configure policy.

Configure Ownerless Group Policy

You can configure a few things in terms of an Ownerless policy. Once you click on Configure policy from the step above, you can configure the following:

1. Notification Options
2. Sender
3. Subject and Email Message text
4. Groups to email/target
5. Once you make all the changes, click Finish

User Experience

Once the policy has been configured, it might take several days for the first email to be sent out. Below is an example of one of the Microsoft 365 Groups left without an Owner.

And here is an email the Team Members receive in their inbox asking them to take ownership of the group.

Once they accept the duty, they become the Owner of the Group/Team/Site.

The post How to avoid Ownerless Teams and Microsoft 365 Groups appeared first on SharePoint Maven.

I’ve mentioned this many times, but sorting email is where productivity dies, even if you have a quick way to do it. There’s nothing better than having Power Automate doing all the work for you, so today, we’ll explore the “when a new email mentioning me arrives” trigger for Office 365.

Let’s see how to use it and how to find it.

Where to find it?

To find it, you can search for the “When a new email mentioning me arrives” trigger or go to the “Office 365 Outlook”:

Select “When a new email mentioning me arrives”:

Here’s what it looks like:

There are advanced options that we’ll explore below, but here’s how to activate them:

Here’s the advanced view.

Let’s check the “advanced” fields since they allow us to filter emails we don’t want to process.

Fields

There are a lot of fields that we can configure. Let’s look at them individually.

Folder

If you leave it blank, Power Automate will look at all incoming emails, but you can define different folders to trigger instead of the inbox. For example, you may have a folder where you send a pre-defined answer to the person that emails you while mentioning you or send you a notification when this happens. The folder is a great way to limit catching all emails that arrive mentioning you so that the Flow doesn’t fire for all emails, so be sure to define it if possible.

“To”, “CC”, “To or CC,” or “From”

These fields were a great idea from Microsoft when creating this trigger. Since we receive many emails, it doesn’t make sense for the trigger to fire each time we receive an email mentioning us. For example, we may only want a subset of the total emails or emails from specific co-workers. With these filters, we don’t need to add large “Condition” or “Switch” actions to filter the emails you want. The trigger does all the work for you.

Just so you know, the trigger will run only if at least one matches, so this is also good if you have substantial limitations on the number of Flows you can run a day.

To use it, separate the emails with “;”. You can have internal and external emails, but keep this field with only the email. When you copy emails from some email clients, you’ll have the name associated with it, so be sure to remove it.

Finally, I want to highlight the “To or CC” field. If you add an email to the “To” field or the “CC” field, the email must contain both for the Flow to trigger. But if you want either one, you can use the “To or CC” field.

Importance

The “importance” field is often ignored, but some organizations can have strict rules on how to use it. If yours is one of them, you can have a Flow that sends you a notification each time a “High” priority email arrives, for example. The available possibilities are:

1. Any
2. High
3. Normal
4. Low

Only with Attachments

For example, if you have a Flow that archives your attachments automatically, you may want to trigger Flows that have them. Having your Flow trigger only when a condition is satisfied makes your life easier because you don’t need to validate, for each email that arrives, if there’s an attachment or not.

Include Attachments

Should the trigger include the attachment information or not? There is, however, something that you should know. Even if you enable this feature, Flow (at the time of writing this article) won’t fetch the attachments correctly. I go into detail in this article, but you need to know that you need to fetch the articles separately using the “Get email” action.

Subject Filter

As with the other fields, we can filter the subject for a specific string, and the Flow will only trigger if that condition is met.

Usage

It’s essential to define what is an email “arriving”. It doesn’t need to be a new email or an email that drops in the inbox. We consider it as “arriving” any email that is moved to a folder, being automatically when it arrives in your inbox, or you move it to another folder. This is super important because it opens the doors to a lot of automation based on moving emails to folders.

This trigger will fire if the emails mention you in the body, so you’ll have access to all properties of that email in the following actions. For example, here’s how to access the email’s subject using a “Compose” action.

Notice that you don’t need to use the “Apply to each” action because you only have one email each time, meaning that if you receive multiple emails where you’re mentioned, your Flow will trigger once per email received.

Please remember that a mention is not the same as having your name. For example, if I write the following email, it won’t be considered a mention.

But the following is:

You need to use the “@“ character to display several suggestions that can be people from this email. Please be careful because some email clients will automatically add the email to the list of senders if it’s mentioned, so be sure you want that person to receive the email.

Limitations

You can’t access emails in shared libraries. You can use the “When a new email arrives in a shared mailbox” trigger to catch emails, but then you need to check int email’s body to see if there’s a mention of you manually.

Also, some emails, like protected emails, could be skipped depending on your tenant configurations. If there’s any issue in the attachments or body of the email, the “When a new email mentioning me arrives” won’t trigger as well.

Recommendations

Here are some things to keep in mind.

Use the filters wisely

Using the filter fields is a fantastic way to trigger your Flow when the condition is met, limiting the number of times the Flow fires. But it’s important to know that the flow triggers only if all filters are met. Test your triggers and ensure they are firing when you want them. Any email that should be parsed but doesn’t fit the filters will be ignored, so it’s a hidden error since there are no error messages or warnings.

Name it correctly

The name is super important in this case since there are a lot of filters. Define in the name what you’re trying to achieve. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate what emails you want to fetch, the ones to ignore the exceptions, and the reasoning behind that. It’s essential to enable faster debugging when something goes wrong. Besides, it’s good to understand the mindset and requirements when building the Flow in case someone else is debugging or improving it.

Back to the Power Automate Trigger Reference.

Photo by Melinda Gimpel on Unsplash

 

Did you know you can trigger Flows for files directly in OneDrive for Business? Let’s say that you have a contract that you want to send for approval. You need to generate a link to the file or attach it to an email, send it to the people who need to approve it, receive notifications, and merge changes. Or you press a button and let Power Automate do the work for you by using OneDrive for Business “For a selected file” trigger that catches these requests.

Let’s see how to use it.

Where to find it?

To find it, you can search for OneDrive for Business “For a selected file” trigger or select “Standard”.

Select “OneDrive for Business”:

Pick OneDrive for Business “For a selected file” trigger:

Here’s what it looks like:

There are no fields to configure, so let’s look at how to use it.

Usage

First, let’s create a Flow called “Approve Contract” (the name will be important later).

Now that we have a Flow with OneDrive for Business “For a selected file” trigger, let’s go to our OneDrive and see how to trigger the automation:

OneDrive for Business did all the work in the backend, and it’s already displaying the Flow for us to run.

When you run, if it’s the first time, you may need to configure/confirm the connections.

After this, the Flow will trigger on Power Automate.

Outputs

The trigger returns a lot of information in a JSON format, although the conversion from JSON is done automatically for you. Here’s an example:

{
    "headers": {
        ...
    },
    "body": {
        "entity": {
            "filePath": "/TMP/Test File11.xlsx",
            "fileUrl": "https://manueltgomescom-my.sharepoint.com/personal/manuel_manueltgomes_com/_layouts/15/Doc.aspx?sourcedoc=%7B5f82b4aa-827d-4c7d-b607-963b0542e7f7%7D&action=default&uid=%7B5f82b4aa-827d-4c7d-b607-963b0542e7f7%7D&ListItemId=372689&ListId=7B5f82b4aa-827d-4c7d-b607-963b0542e7f7&odsp=1&env=prod",
            "ID": 372689,
            "itemUrl": "https://manueltgomescom-my.sharepoint.com/personal/manuel_manueltgomes_com/_layouts/15/Doc.aspx?sourcedoc=%7B5f82b4aa-827d-4c7d-b607-963b0542e7f7%7D&action=default&uid=%7B5f82b4aa-827d-4c7d-b607-963b0542e7f7%7D&ListItemId=372689&ListId=8e3fcb4a-4b52-4790-a96b-89f9e426aa90&odsp=1&env=prod",
            "fileName": "Test File11.xlsx",
            "FileId": "372689"
        }
    }
}

Notice that we only got the information about the file, not the file itself. Other triggers like OneDrive for Business “When a file is created Trigger”, for example, will return the file information we can use. Still, the OneDrive for Business “For a selected file” will return the file ID. We can then use that ID and the “Get File Content Action” action to get the file to parse.

Limitations

If you build the Flows in the “My Flows” section, only you will see them in OneDrive for Business. This is not a limitation per se, but it’s something that you should be aware of, but there’s no real impact since you’re triggering the Flow in files that are in your OneDrive for Business and not a shared place like SharePoint where multiple people have access to the same file.

Recommendations

Here are some things to keep in mind.

Don’t use this for synchronization

I see many questions regarding the synchronization of files. OneDrive for Business “For a selected file” trigger is an excellent target to catch files that need replication. But I would strongly advise you not to do it. Synchronization of files is an amazingly complex topic in computer science, and we are all super when something doesn’t synchronize properly. If the trigger fails, data will be out of sync, and Power Automate won’t rerun it. If the data is changed on the destination folder, you already have a problem that will only worsen over time.

Name it correctly

The name is super important since we need to provide context for what we will do with the file. Keeping the name “For a selected file” won’t give any helpful context. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Additional contracts to the name

Since you’ll be seeing the flow’s name in OneDrive for Business, and it’s your only clue as to what it will do to the selected item, it’s essential o keep the names as straightforward as possible.

In this example, “Request sign-off” is not clear enough. Request sign-off of what? What will happen? Name it so that, even with only a few characters, people will know what happens when they trigger the automation.

Finally, this is the only exception I see to not using environment clues like “PROD” in the name, for example. Space is limited, so if the name is too long, it may be truncated.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate what you’re expecting, why the Flow should be triggered, and what the data will be used. It’s essential to add comments when limiting the trigger with some custom rulessince these are not prominent in the UI, and people may get confused as to why the Flow doesn’t trigger when it’s simply a rule preventing it from doing so. It’s essential to enable faster debugging when something goes wrong.

Finally, let people know why you’re choosing the parameters you configured. For example, why do you select that folder if you have a folder defined? It may make sense now, but not in a few months.

An automated trigger is better than a scheduled one

Sometimes people are tempted to use scheduled triggers that pool the resources once in a while. This way, they can control when the information is fetched and save many Power Automate “triggers” if their quota is low. However, even if it isn’t, it may be more efficient to do batch tasks than once by one. I understand, and in some cases, I can agree, but it brings a lot of difficulties in the process. For example, you may need to keep track of what changed from the last run until this one so that some things may get lost. Also, you’re forcing something to happen periodically, even if there’s no data.

I always recommend using these “automatic” triggers instead, where they trigger one by one, but only when there’s data, so you’re always sure you get something to do. Also, debugging triggers that parse a single data point instead of multiple simultaneously is much easier. If something fails on one, then you can fix the Flow and repeat the process. But while parsing multiple ones, things can get a lot harder.

Back to the Power Automate Trigger Reference.

Photo by Priscilla Du Preez on Unsplash

Did you know you can trigger Flows for files directly in SharePoint? Let’s say that you have a contract that you want to send for approval. You need to generate a link to the file or attach it to an email, send it to the people who need to approve it, receive notifications, and merge changes. Or you press a button and let Power Automate do the work for you by using SharePoint “For a selected file” trigger that catches these requests.

Let’s see how to use it.

Where to find it?

To find it, you can search for SharePoint “For a selected file” trigger or select “Standard”:

Select “SharePoint”:

Pick “For a selected file” trigger:

Here’s what it looks like.

Let’s see the fields that we need to configure.

Fields

There are a few fields to configure so that you can take advantage of them.

Site address

We need a place to search for changes, so we need to configure the site address. There’s no way to select “all sites,” but this is a broad search parameter since a site could already have a lot of activity. If you want more sites, you need to replicate the trigger to consider all the sites you need.

Library Name

Within sites, there are multiple libraries, so we need to indicate what library we want. As before, we cannot have an “all libraries” filter, so if you have a lot of libraries, you need to duplicate the trigger for all of them.

Usage

Now that we have the site and library name configured, let’s see how to use it. First, let’s create a Flow with a trigger named “Approve Contract” (the name will be important later).

Now that we have a Flow with SharePoint “For a selected” trigger, let’s go to the SharePoint document library and see how to trigger the automation.

SharePoint did all the work in the backend, and it’s already displaying the Flow for us to run. No configuration is needed on the SharePoint side.

When you run, if it’s the first time, you may need to configure/confirm the connections.

After this, the Flow will trigger on Power Automate.

Outputs

The trigger returns a lot of information in a JSON format, although the conversion from JSON is done automatically for you. Here’s an example:

{
    "headers": {
        ...
    },
    "body": {
        "entity": {
            "ID": 4,
            "itemUrl": "https://manueltgomescom.sharepoint.com/sites/Test/_layouts/15/Doc.aspx?sourcedoc=%7B5f82b4aa-827d-4c7d-b607-963b0542e7f7%7D&action=edit&uid=%3c139851-251a-4ee0-907f-24dcfd5c29c1%7D&ListItemId=4&ListId=%ed455c5b-349d-4c44-bc20-ec45ce49c417%7D&odsp=1&env=prod",
            "fileName": "Test Document.docx",
            "FileId": "4"
        }
    }
}

Notice that we only got the information about the file, not the file itself. Other triggers like SharePoint’s “When a file is created (properties only)” trigger, for example, will return the file metadata that we can use, but the SharePoint “For a selected file” will return the item ID. We can then use that ID and use the “Get File Metadata Action” action to get the file metadata for the file to parse.

Limitations

If you build the Flows in the “My Flows” section, only you will see them in SharePoint. This is not a limitation per se, but it’s something that you should be aware of. If you want other users to see the Flow, you need to share it with them like this:

Add the names or emails:

The Flow will not show in their SharePoint document library.

Recommendations

Here are some things to keep in mind.

Don’t use this for synchronization

I see many questions regarding synchronization between SharePoint document libraries, and SharePoint “For a selected file“ trigger is an excellent target to catch files that need replication. But I would strongly advise you not to do it. Synchronization of files is an amazingly complex topic in computer science, and we are all super when something doesn’t synchronize properly. If the trigger fails, data will be out of sync, and Power Automate won’t rerun it. If the data is changed on the destination folder, you already have a problem that will only worsen over time.

Name it correctly

The name is super important since we need to provide context for what we will do with the file. Keeping the name “For a selected file” won’t give any helpful context. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Additional contracts to the name

Since you’ll be seeing the flow’s name in SharePoint, and it’s your only clue as to what it will do to the selected item, it’s essential o keep the names as straightforward as possible.

In this example, “Request sign-off” is not clear enough. Request sign-off of what? What will happen? Name it so that, even with only a few characters, people will know what happens when they trigger the automation.

Finally, this is the only exception to not using environment clues like “PROD” in the name. Space in SharePoint is limited; if the name is too long, it may be truncated.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate what you’re expecting, why the Flow should be triggered, and what the data will be used. It’s essential to add comments when limiting the trigger with some custom rulessince these are not prominent in the UI, and people may get confused as to why the Flow doesn’t trigger when it’s simply a rule preventing it from doing so. It’s essential to enable faster debugging when something goes wrong.

Finally, let people know why you’re choosing the parameters you configured. For example, why do you select that folder if you have a folder defined? It may make sense now, but not in a few months.

An automated trigger is better than a scheduled one

Sometimes people are tempted to use scheduled triggers that pool the resources once in a while. This way, they can control when the information is fetched and save many Power Automate “triggers” if their quota is low. However, even if it isn’t, it may be more efficient to do batch tasks than once by one. I understand, and in some cases, I can agree, but it brings a lot of difficulties in the process. For example, you may need to keep track of what changed from the last run until this one so that some things may get lost. Also, you’re forcing something to happen periodically, even if there’s no data.

I always recommend using these “automatic” triggers instead, where they trigger one by one, but only when there’s data, so you’re always sure you get something to do. Also, debugging triggers that parse a single data point instead of multiple simultaneously is much easier. If something fails on one, then you can fix the Flow and repeat the process. But while parsing multiple ones, things can get a lot harder.

Back to the Power Automate Trigger Reference.

Photo by Brienne Hong on Unsplash

 

Although Excel is known for being a standalone application, there’s a lot that we can do with Power Automate and tasks that we can automate. Today we’ll explore how to automate the update of rows in an Excel file by using the “Update a Row” action. This action works for files in SharePoint, Teams , or OneDrive for Business so let’s look at how to use it.

Where to find it?

You can find it under “Standard”.

Pick “Excel Online”. You may see or need to expand the list depending on your suggestions.

Select the “Update a Row” action:

Here’s what it looks like.

Now that we know how to find it let’s understand how to use it.

Fields

You can find some fields to configure before using the “Update a row” action, so let’s look at them individually.

Location

As mentioned before, we can use SharePoint, Teams, or OneDrive For Business to find our Excel files. To do it, select the location where you want to find the data. For example, for OneDrive For Business:

As you see above, you have a lot of “Groups” sites that are commonly Microsoft Teams sites.

If you’re looking for SharePoint sites, they are commonly displayed like this:

Since they all behave the same way, I’ll refer only to SharePoint sites from now on, but keep in mind that you can also use Microsoft Teams and OneDrive For Business. I’ll highlight the differences when appropriate.

Document Library

Since SharePoint sites can have multiple document libraries, we need to pick them from the list.

There’s always one created by default called “Documents” or something different depending on your language, but since it’s a mandatory field, you need to pick it; otherwise, you won’t be able to choose the file from the following field.

The same behavior occurs for OneDrive For Business, even if it’s not possible to create document libraries. Microsoft creates a document library that you can pick from the list.

File

Now that we have the locations defined, let’s look at a file. You can pick the file in the “File Browser”.

Or you can provide the path yourself, although I discourage this. Although you can do it, Power Automate sometimes returns strange errors related to the path, especially if the file has special characters. It’s always recommended that you can, of course, select the file directly in Power Automate’s UI.

Table

Power Automate requires the information inside an Excel table to access the data. To find the table, you can go to your Excel file, select a row and then check if you have a “Table Design” tab in the ribbon.

If you don’t, then chances are that you need to create a table in Excel before you can continue. Select the table in the dropdown.

The Excel file can have multiple tables, so it’s essential to define the one you want to manipulate the data. Finally, you can provide the table’s name as a “dynamic field,” but to ensure that your Flows always work, I recommend picking it from the dropdown.

Key Column

Now that we have a file, we need to tell Power Automate how to identify the row we want to update. Since there could be multiple tables in Excel, Microsoft required a key column that identifies the row we want to update inside the defined table. If you don’t have one, you can use the “Add a key column to a table” action that will do it for you.

Since we provided the path before, Power Automate checks the file and gives us a list of the columns in the provided table.

Notice that once we defined those fields, Power Automate added in the background additional fields for us to add information. This is only possible because we are not dynamically providing any of the fields, allowing Power Automate to access the file, get the columns, and add them as fields.

Please be aware that if you dynamically define the path, table, or file, you need to provide the “key column” name since Power Automate won’t find the list of columns before the Flow runs.

In case you don’t know what “Key Columns” are, I have an article here that explains them in a lot of detail.

Key Value

Finally, we need a key value that identifies the row we want to update from the table. Notice that we’re updating the item by providing an identifier, not the row number, since the same row can be inside multiple tables. The key value needs to be unique within the “key column” and should be a number.

Usage

Let’s look at a test file with anonymous employee information.

Let’s say we want to promote a person and update their salary to another value. Here’s what it looks like:

After we run, here’s what the Excel looks like:

Be careful to ensure that you have proper error handling, especially after this action, since the action will fail if the ID cannot be found with an exception like this:

No row was found with Id '3'.

Advanced Properties

As you can see, there are also advanced properties that we can take advantage of. When you toggle it, you get an additional field as follows:

This field will define, for the dates fields, how the date should be provided. Remember that Excel internally stores the dates in a numeric format, even if it’s presented to you differently. So Power Automate enables us to define the format of the data with two options:

The “Serial Number” is the “native” way that Excel stores the date, meaning that if you want to provide a date, you need to provide a number. With “ISO 8601” you give a date that makes more sense to us but in a fixed format. Functions in Power Automate , like the utcNow() function, return the date already in that format. Still, if you have a date in a string, you can use the formatDateTime function to format it correctly.

Non-intuitive behaviors

If you leave the values empty, it doesn’t mean you want to delete the values in Excel for that column. It means that you don’t want to make changes to that field. Notice the example above where I only filled in the “Salary” field since that was the only one I wanted to update.

If you want to remove the value from a column, please use the “null” special value that will signal to Power Automate that the information should be removed from the selected item.

Limitations

It could have been a temporary problem, but I got a lot of issues with some key column names, like “identifier”, for example. If you’re having issues with Flow returning strange error messages, consider changing to something like “Key Column”.

At this point should not be considered a limitation anymore, the “Update a row” action will only support Excel Workbook (xlsx) files, so you need to convert your Excel file before you can use the action.

Recommendations

Here are some things to keep in mind.

Name it correctly

In this case, the name is super important to provide the context of what we’re updating. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment

Adding a comment will also help avoid mistakes since we can indicate why that row is updated in Excel. Also, if you’re adding any elements dynamically, it’s a good practice to explain how the values are obtained. Indicate where the path comes from, for example, if it’s calculated and how. It’s essential to enable faster debugging when something goes wrong.

Always deal with errors

Have your Flow fail graciously and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked-for a while or generate even worse errors. I have a template that you can use to help you make your Flow resistant to issues. You can check all details here. Remember that if the “Key Value” doesn’t exist in the “Key Column,” the action will return an error, so it’s essential to have a parallel branch that catches these cases.

Back to the Power Automate Action Reference.

Photo by Peter Herrmann on Unsplash

 

I have to admit that this is a strange trigger because it mixes items and files, and it doesn’t have a “when created” counterpart. It can be helpful depending on your workflow, so today, we’ll explore the “When an item or a file is modified” trigger and how to take advantage of it.

Where to find it?

To find it, search for the “When an item or a file is modified” trigger or go to “Standard”.

Select “SharePoint”.

Pick the “When an item or a file is modified” trigger.

Here’s what it looks like.

There are some advanced options that you can explore. To add them, press here:

Here’s what it looks like with the options expanded:

Let’s look at the fields and how we can configure them.

Fields

There are a few fields to configure so that you can take advantage of them.

Site address

We need a place to search for changes, so we need to configure the site address. There’s no way to select “all sites,” but this is a broad search parameter since a site could already have a lot of activity. If you want more sites, you need to replicate the trigger to consider all the sites you need.

List or Library Name

Within sites, there are multiple libraries, so we need to indicate what library we want. As before, we cannot have an “all libraries” filter, so if you have a lot of libraries, you need to duplicate the trigger for all of them.

It will also display all the lists on the site; as for the document libraries, you cannot have an “all lists” choice.

Folder

This one is optional and should be used wisely. If we want to get changes from all files, by all means, keep it empty, but it’s always good to limit a bit the number of Flows that we trigger. If you can have the trigger look for folders down the line, it will help you:

1. Having fewer exceptions to parse and reducing the number of folders also limits the number of types of files that you need to look at.
2. Fewer triggers, meaning that you keep your Power Automate usage under control. Remember that triggers are not unlimited, so keeping them controlled will go a long way to avoid having limitation issues in the future.

Limit Columns by View

You can limit the columns that you want to be returned by defining a view in SharePoint, and by doing so, you can speed up your Flow because it will only return a smaller subset of data. But remember that it won’t consider any filters you have defined in the view. As the name indicates, it will only look at the columns in the view, not the data, so it will always return changes to files and items that may not be visible in your view.

Usage

Here’s an example of a document library I created, and we want to check changes to those files.

Since we leave the folder field empty, Power Automate will look at all files in the document library.

What is a modification?

But to understand when the trigger will fire, we need to understand what is a “modification”. A “modification” is when you change the data associated with the item’s metadata and not the file itself. Adding more text to a word file is not a change.

When you see a document library, you’ll notice that the folder structure looks much like the item structure, and that’s for a good reason. They are the same, but since they are different types, SharePoint displays them in a way that makes sense to the user.

Here’s an example of the document library in the example above:

We even have the “+ Add column” option as we have on the list item view.

This is because the document library is a view behind the scenes, meaning we can add additional items to a list, so let’s add a new column called “Status” to test.

Now let’s edit the value. When you’re a list item, you will click on the row, but since this is a file, it will open the file itself and not its properties. You need to do the following:

You’ll notice that we’re not touching the file. We’re not editing the file itself. We’re editing the list item, in this case, a file, because it’s a document library. And this is when the trigger will fire because there was a change to the file.

So the “When an item or a file is modified” trigger will fire when you edit the fields in the list regardless of their type (item or file). I know it’s not intuitive since we think modifying a file by editing it, but this is not how this trigger works.

If you select the folder ensure it is in the document library

Finally, be careful when selecting the folder in case you need it. When you choose a document library, the UI won’t filter the folder list to the ones inside that library and will always list for the site. For example:

Be sure to pick folders inside the document library because Power Automate won’t return an error. The Flow won’t fire because it’s impossible for the file to be in two folders simultaneously.

Outputs

The “When an item or a file is modified” trigger outputs depend on the parameters you pick. For example, in the case above, we have a column “Status”, so the trigger will return it:

But if we pick a list, the UI will adapt and provide the columns for that list.

Non-intuitive behaviors

I mentioned above, but the most non-intuitive behavior for the “When an item or a file is modified” trigger is that the files could be modified (for example, adding a new row in an Excel file). Still, the trigger won’t consider it as a “modification”. It will only consider modifications when you change the columns associated with that document library, as it was a “simple” list.

Limitations

You can’t pick “all document libraries”, so you always have to pick a list or a document library from the list. It’s not really a limitation since it’s a good compromise, but if you have multiple document libraries, you need to replicate the Flow for each document library.

Recommendations

Here are some things to keep in mind.

Don’t use this for synchronization

I see many questions regarding synchronization between SharePoint lists, and the “When an item or a file is modified“ trigger is an excellent target to catch items that need replication. But I would strongly advise you not to do it. Synchronization of items is an amazingly complex topic in computer science, and we are all super when something doesn’t synchronize properly. If the trigger fails, data will be out of sync, and Power Automate won’t rerun it. If the data is changed on the destination list, you already have a problem that will only worsen over time.

Name it correctly

The name is super important since the trigger has a lot of configurations. We should name it, so it’s clear what information we’re getting and why. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate what you’re expecting, why the Flow should be triggered, and what the data will be used. It’s essential to add comments when limiting the trigger with some custom rulessince these are not prominent in the UI, and people may get confused as to why the Flow doesn’t trigger when it’s simply a rule preventing it from doing so. It’s essential to enable faster debugging when something goes wrong.

Finally, let people know why you’re choosing the parameters you configured. For example, why do you select that folder if you have a folder defined? It may make sense now, but not in a few months.

An automated trigger is better than a scheduled one.

Sometimes people are tempted to use scheduled triggers that pool the resources once in a while. This way, they can control when the information is fetched and save much Power Automate “triggers” if their quota is low. However, even if it isn’t, it may be more efficient to do batch tasks than once by one. I understand, and in some cases, I can agree, but it brings a lot of difficulties in the process. For example, you may need to keep track of what changed from the last run until this one so that some things may get lost. Also, you’re forcing something to happen periodically, even if there’s no data.

I always recommend using these “automatic” triggers instead, where they trigger one by one, but only when there’s data, so you’re always sure you get something to do. Also, debugging triggers that parse a single data point instead of multiple simultaneously is much easier. If something fails on one, then you can fix the Flow and repeat the process. But while parsing multiple ones, things can get a lot harder.

Back to the Power Automate Trigger Reference.

Photo by Christopher Bill on Unsplash

 

Although Excel is known for being a standalone application, there’s a lot that we can do with Power Automate and tasks that we can automate. Today we’ll explore getting a row in an Excel file using the “Get a row” action. This action works for files in SharePoint, Teams, or OneDrive for Business so let’s look at how to use it.

Where to find it?

You can find it under “Standard”:

Pick “Excel Online”:

Select “Get a Row” action:

Here’s what it looks like.

Now that we know how to find it let’s understand how to use it.

Fields

You can find some fields to configure before using the “Get a row” action, so let’s look at them individually.

Location

As mentioned before, we can use SharePoint, Teams , or OneDrive For Business to find our Excel files. To do it, select the location where you want to find the data. For example, for OneDrive For Business:

As you see above, you have a lot of “Groups” sites that are commonly Microsoft Teams sites.

If you’re looking for SharePoint sites, they are commonly displayed like this:

Since they all behave the same way, I’ll refer only to SharePoint sites from now on, but keep in mind that you can also use Microsoft Teams and OneDrive For Business. I’ll highlight the differences when appropriate.

Document Library

Since SharePoint sites can have multiple document libraries, we need to pick them from the list.

There’s always one created by default called “Documents” or something different depending on your language, but since it’s a mandatory field, you need to pick it; otherwise, you won’t be able to choose the file from the following field.

The same behavior occurs for OneDrive For Business, even if it’s not possible to create document libraries. Microsoft creates a document library that you can pick from the list.

File

Now that we have the locations defined, let’s look at a file. You can pick the file in the “File Browser”.

Or you can provide the path yourself, but I discourage this. Although you can do it, Power Automate sometimes returns strange errors related to the path, especially if the file has special characters. It’s always recommended that you can, of course, select the file directly in Power Automate’s UI.

Table

Power Automate requires the information inside an Excel table to access the data. To find the table, you can go to your Excel file, select a row and then check if you have a “Table Design” tab in the ribbon.

If you don’t, then chances are that you need to create a table in Excel before you can continue. Select the table in the dropdown.

The Excel file can have multiple tables, so it’s important to define the one you want to manipulate the data. Finally, you can provide the table’s name as a “dynamic field,” but to ensure that your Flows always work, I recommend picking it from the dropdown.

Key Column

Now that we have a file, we need to tell Power Automate how to identify the row we want to get. Since there could be multiple tables in Excel, Microsoft required a “Key Column” that identifies the row we want to get inside the defined table. If you don’t have one, you can use the “Add a key column to a table” action that will do it for you.

Since we provided the path before, Power Automate checks the file and gives us a list of the columns in the provided table.

Please be aware that if you dynamically define the path, table, or file, you need to provide the “Key Column” name since Power Automate won’t find the list of columns before the Flow runs, so it can’t present you with a list.

In case you don’t know what “Key Columns” are, I have an article here that explains them in a lot of detail.

Key Value

Finally, we need a key value that identifies the row we want to get from the table. Notice that we’re getting the item by providing an identifier, not the row number, since the same row can be inside multiple tables. The key value needs to be unique within the “Key Column” and should be a number.

Usage

Let’s look at a test file with anonymous employee information.

Let’s say you want the information for the employee with ID “2”. For our example, we’ll use OneDrive for Business, so here’s what it looks like:

After we run, we get the following information:

Be careful to ensure that the ID that you’re picking exists in the table otherwise, you’ll get an error. For example, if we don’t have the ID “3” in the table, we’ll get the following exception:

No row was found with Id '3'.

You can find the fields in the “Dynamic Fields” tab if you want to access them. For example, let’s use a compose action:

Please note that you won’t be able to do this if you use dynamic fields for any of the elements, like the path, for example. Since Power Automate can only know the structure of the file while it’s running, it’s not possible to add the fields as a preview to pick them in the UI.

Finally, notice that the values returned are not formatted and, in some cases, don’t have the format you would expect. For example, dates are returned as numbers, so if you want to convert the Excel number dates into “real” dates, here’s an article on how to do it. Or if you need to check this article to convert the dates up until the second. There’s also an article that shows you how to format the date and show information in other languages.

Please also consider that numbers don’t contain formatting like currency or custom Excel formats that you define. If you want to configure the numbers in other formats, you need to use the “Format Number” action after you get the data.

Limitations

At this point should not be considered a limitation anymore; the “Get a row” action will only support Excel Workbook (xlsx) files, so you need to convert your Excel file before you can use the action.

Recommendations

Here are some things to keep in mind.

Name it correctly

In this case, the name is super important to provide the context of what we’re trying to get. For example, if you’re trying to get information about an employee, mention that in the name of the action. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment

Adding a comment will also help avoid mistakes since we can indicate the reason for getting the Excel file row. Also, if you’re adding any elements dynamically, it’s a good practice to explain how the values are obtained. Indicate where the path comes from, for example, if it’s calculated and how. It’s essential to enable faster debugging when something goes wrong.

Always deal with errors

Have your Flow fail graciously and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked-for a while or generate even worse errors. I have a template that you can use to help you make your Flow resistant to issues. You can check all details here. Remember that if the “Key Value” doesn’t exist in the “Key Column,” the action will return an error, so it’s essential to have a parallel branch that catches these cases.

Back to the Power Automate Action Reference.

Photo by Becca Tapert on Unsplash

 

So you have a URL and want to get parts of it. One way would be to use the split function or indexOf function and parse it yourself, but there’s a better way by using one of the “uri” functions available in Power Automate like the “uriHost”, “uriPath” and “uriPathAndQuery” functions to name a few. Today we’ll explore the “uriQuery” function and, as the name indicates, get the URL’s query string (also known as parameters). Let’s see how to use it.

Where to find it?

You can find the function in every action where a formula is supported. For example, let’s look at a “Compose” action:

As you can see, we can auto-complete by using the “tab” key. Let’s look at how to use it.

Usage

It follows a simple pattern.

1. String with the URL

Let’s start with a simple example:

Let’s try with a big URL.

String 'https://manueltgomes.com/area/microsoft/powerautomate/'

will return 

""

The URL will return an empty string if it doesn’t have any query string.

Let’s try with parameters and see what we get:

String 'https://manueltgomes.com?query=test'

will return

?query=test

Notice that if you used the “uriPathAndQuery” function above, you would get something slightly different:

String 'https://manueltgomes.com?query=test'

will return

/?query=test

This is because every function that deals with the path in some way, like “uriPath” or “uriPathAndQuery” consider the “empty” path as “/“ and not as an empty string, so the “/“ is always included.

Let’s try an URL with a path:

String 'https://manueltgomes.com/area/microsoft/powerautomate/?query=test'

will return 

?query=test

As expected, the path would be ignored, and we only get the query string.

Finally, what about a string that is not a valid URL?

String 'this is not a valid URL'

will return

Unable to process template language expressions in action 'Parse_the_URL' inputs at line '0' and column '0': 'The template language function 'uriQuery' expects its parameter to be a well-formed absolute URI. The provided value was 'this is not a valid URL'. Please see https://aka.ms/logicexpressions#uriQuery for usage details.'.

Notice that we need a valid URL to be provided to the function, so be sure you validate the string before running the function; otherwise, your Flow will fail.

Limitations

Depending on the size of your string, your expression may return an error, even if it’s correct. Please note that the expressions have a max size of 8,192 characters. If you have an expression even bigger than 1000, I would strongly advise you to break it into smaller manageable formulas.

Recommendations:

Here are some things to keep in mind.

If you want to parse an URL, don’t do it manually

Always use the “uri” functions that Power Automate provides you. They do a lot of work and consider edge cases that you may not consider. Also, using one function that does all the work for us is much easier than doing all the work ourselves.

Use “debug” compose actions

I recommend using Compose actions to have the values that go “in” the function. This way, if the value doesn’t make sense, you can understand, based on the parameters, why it was calculated that way. It’s useful, especially if you change something in the URL before parsing it, so you can see the value before the function parses it.

Don’t nest

There’s no real reason to do it, but if you find yourself in a situation where you have nested “uriQuery” functions in a formula, you should review it and make everything more straightforward. If you nest it, the Flow will fail since the result of the function is not a valid URL, so be sure not to do it.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate why you are trying to find the element and what it means. It may look obvious initially but it will not be in a few months or years. It’s essential to enable faster debugging when something goes wrong.

Sources:

Microsoft’s uriQuery Function Reference

Back to the Power Automate Function Reference.

Photo by Towfiqu barbhuiya on Unsplash

SharePoint’s lists are excellent for keeping data, but one accidental delete could generate a lot of headaches, especially if you don’t notice it. That’s why Microsoft developed the “When an item is deleted” trigger, where you can define Flows that do actions when an item is deleted, like a notification to someone to check the “Recycle bin” and see if the item should have been deleted or not.

Let’s check how to take advantage of SharePoint’s “When an item is deleted” trigger.

Where to find it?

To find it, you can search for the “When an item is deleted” trigger or go to “Standard”:

Select “SharePoint”:

Pick the “When an item is deleted” trigger.

Here’s what it looks like.

Usage

It’s pretty easy to configure. You select the site and the list you want to check, and that’s it. For example, if we want to check our “Test Users” list, we’ll do the following:

It will trigger as soon as an item is deleted, but I want to highlight the outputs of this trigger. It won’t return the elements of the item, so you won’t have access to all columns like in the “When an item is created or modified” trigger, for example. You will get the following information:

It makes sense to return this data since the trigger will fire after the item is deleted, so it won’t have access to its information. It will provide information about the deletion, like who deleted it and when.

Non-intuitive behaviors

As mentioned above, not returning all list elements could be considered a non-intuitive behavior. Still, there’s another one I would like to highlight concerning the data returned.

As you can see above, you have the “Is Folder” that is kind of strange on a list, right? We’re dealing with list items, not folders and files, but there’s the indication of “Is Folder”. This is because you can add folders to a list to organize your items. I go into a lot of detail here , so take a look if you want to learn how to use them.

Finally, there’s the possibility to configure SharePoint to keep all changes to the list items. If you don’t know how to do it, check my article on SharePoint’s best practices in the section “Versions”. We can take advantage of this version history and use the “Send an HTTP request to SharePoint “ action to get it. If you don’t know how to do it, here’s an article.

The issue with this is that you won’t be able to do it. You’ll get the following error message:

Item does not exist. It may have been deleted by another user.

Again, the trigger will fire after is deleted, so you won’t have access to its elements unless you manually restore it from the “Recycle Bin”. However, one could consider a “deletion” as a change to the item’s status and worth keeping a version mention of it, but I can understand why Microsoft chose this behavior.

Limitations

The “When an item is deleted” trigger can only be used by site collection admins of the site where the list is located, meaning that if you’re not a site admin, this trigger won’t fire, so keep this in mind in case you’re having issues with the trigger.

Recommendations

Here are some things to keep in mind.

Don’t use this for synchronization

I see many questions regarding synchronization between SharePoint lists, and the “When an item is deleted“ trigger could be an excellent target to catch items that need deleting. But I would strongly advise you not to do it. Synchronization of items is an amazingly complex topic in computer science, and we are all super when something doesn’t synchronize properly. If the trigger fails, data will be out of sync, and Power Automate won’t rerun it. If the data is changed on the destination list, you already have a problem that will only worsen over time.

Name it correctly

The name is super important since the trigger can be used on any list you can access. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate what you’re expecting, why the Flow should be triggered, and what the data will be used. It’s essential to add comments when limiting the trigger with some custom rulessince these are not prominent in the UI, and people may get confused as to why the Flow doesn’t trigger when it’s simply a rule preventing it from doing so. It’s essential to enable faster debugging when something goes wrong.

Finally, let people know why you’re choosing the parameters you configured. For example, why do you select that folder if you have a folder defined? It may make sense now, but not in a few months.

An automated trigger is better than a scheduled one

Sometimes people are tempted to use scheduled triggers that pool the resources once in a while. This way, they can control when the information is fetched and save much Power Automate “triggers” if their quota is low. However, even if it isn’t, it may be more efficient to do batch tasks than once by one. I understand, and in some cases, I can agree, but it brings a lot of difficulties in the process. For example, you may need to keep track of what changed from the last run until this one so that some things may get lost. Also, you’re forcing something to happen periodically, even if there’s no data.

I always recommend using these “automatic” triggers instead, where they trigger one by one, but only when there’s data, so you’re always sure you get something to do. Also, debugging triggers that parse a single data point instead of multiple simultaneously is much easier. If something fails on one, then you can fix the Flow and repeat the process. But while parsing multiple ones, things can get a lot harder.

Back to the Power Automate Trigger Reference.

Photo by Gareth Harper on Unsplash

If you do operations in Excel using Power Automate, you know that you need to use a “Key Column” and a “Key Value” pair to identify the item you want to operate on. For example, if you use the “Delete a row” action, you need to provide it so that Power Automate knows what element to delete. But what about if you don’t have a “Key column”? You can create one yourself directly in the Excel file, but Power Automate also has the “Add a key column to a table” action that can do that for us automatically.

In case you don’t know what “Key Columns” are, I have an article here that explains them in a lot of detail.

Let’s look at how to use it.

Where to find it?

You can find it in “Standard”.

Select “Excel Online”:

Pick the “Add a key column to a table” action:

Here’s what it looks like.

Now that we know how to find it let’s understand how to use it.

Fields

You can find some fields to configure before using the “Add a key column to a table” action, so let’s look at them individually.

Location

As mentioned before, we can use SharePoint, Teams , or OneDrive For Business to find our Excel files. To do it, select the location where you want to find the data. For example, for OneDrive For Business:

As you see above, you have a lot of “Groups” sites that are commonly Microsoft Teams sites.

If you’re looking for SharePoint sites, they are commonly displayed like this:

Since they all behave the same way, I’ll refer only to SharePoint sites from now on, but keep in mind that you can also use Microsoft Teams and OneDrive For Business. I’ll highlight the differences when appropriate.

Document Library

Since SharePoint sites can have multiple document libraries, we need to pick them from the list.

There’s always one created by default called “Documents” or something different depending on your language, but since it’s a mandatory field, you need to pick it; otherwise, you won’t be able to choose the file from the following field.

The same behavior occurs for OneDrive For Business, even if it’s not possible to create document libraries. Microsoft creates a document library that you can pick from the list.

File

Now that we have the locations defined, let’s look at a file. You can pick the file in the “File Browser”.

Or you can provide the path yourself, but I discourage this. Although you can do it, Power Automate sometimes returns strange errors related to the path, especially if the file has special characters. It’s always recommended that you can, of course, select the file directly in Power Automate’s UI.

Table

Power Automate requires the information inside an Excel table to access the data. To find the table, you can go to your excel file, select a row and then check if you have a “Table Design” tab in the ribbon.

If you don’t, then chances are that you need to create a table in Excel before you can continue. Select the table in the dropdown.

The Excel file can have multiple tables, so it’s essential to define the one you want to create the column. Finally, you can provide the table’s name as a “dynamic field,” but to ensure that your Flows always work, I recommend picking it from the dropdown.

Key Column

Now that we have the file adequately configured, we can define the “Key Column” we want to add to the file. Notice that, while ugly, Power Automate suggests a name, but picking the name to use is up to you. I recommend adding something more descriptive that fits more with the naming convention of the other columns, but any name will do.

Usage

Let’s look at a test file with anonymous employee information.

For our example, we’ll use OneDrive for Business, so here’s what it looks like:

Notice that we changed the name of the column we want to create. After we run, let’s look at the file, and we see a new column called “Key Column” created:

Be careful because if you run the “Add a key column to a table” action again, you’ll get an error since the column already exists. Here’s the error if we rerun it:

Column 'Key Column' already exists in the table 

Let’s look at some non-intuitive behaviors.

Non-intuitive behaviors

Since we’re creating a “Key Column”, we would expect that the column would be populated with data, like a unique sequence of numbers we can reference later, but it’s not what happens. Power Automate creates the column and leaves it empty, meaning we’re roughly creating a “normal” column. We need to manually fill the column with unique values we can reference later.

Please note that when you’re creating a column using the “Add a key column to a table” action, you’re only creating a “normal” column, meaning that other columns could exist that can be used as “Key Column” in Flows. A “Key Column” is only a column that contains unique values that can be referenced, and there’s no way to define in Excel that a column is a key column, like when it happens on a database system, for example.

Limitations

Although at this point should not be considered a limitation anymore, the “Add a key column to a table” action will only support Excel Workbook (xlsx) files, so you need to convert your Excel file before you can use the action.

Recommendations

Here are some things to keep in mind.

Name it correctly

In this case, the name is super important to provide the context of where we’re adding the key column, for example. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment.

Adding a comment will also help avoid mistakes since we can indicate why the Excel file “Key Column” is added. Also, if you’re adding any elements dynamically, it’s a good practice to explain how the values are obtained. Indicate where the path comes from, for example, if it’s calculated and how. It’s essential to enable faster debugging when something goes wrong.

Always deal with errors.

Have your Flow fail graciously and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked-for a while or generate even worse errors. I have a template that you can use to help you make your Flow resistant to issues. You can check all details here. Remember that if the “Key Column” already exists in the Excel file, the action will return an error. This could be because you ran a Flow with a “Add a key column to a table” action, for example, so it’s essential to have a parallel branch that catches these cases.

Don’t forget to fill in the column after creating it.

Since Power Automate creates the “Key Column” empty, you need to add the values to the Excel files; otherwise, when you try to use another action that uses the “Key Column”, like the “Delete a row” action, for example, you’ll get an error.

Back to the Power Automate Action Reference.

Photo by Wolfgang Rottmann on Unsplash