• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

GRISCHKE SOLUTIONS

Website and Software Development | IT | Office

  • Home
  • About
  • Services
    • Web Development & Hosting
    • IT, Networking and Cloud Solutions
    • Email and Office Solutions
    • Design Services
    • Quality Management
    • Document Management
    • Performance Dashboard
    • HR Management
    • Payroll Solutions
    • IT Support
    • GDPR
  • Blog
    • Start Jaguar Engine Remotely with Siri
    • Backup and restore IONOS DNS records using Power Automate, API, including SOA record
    • How to build an Email Parser in Microsoft Flow (Extract text from email body)
    • Windows 10 1803 new OEM (or ESP) Partition
    • Unprotect Excel Worksheets without Password
    • Data protection law is changing – you need to act now
    • Calculate Ratio Between Two Numbers
    • Charts in SharePoint
    • Microsoft SharePoint Services – What you need to know
    • Hide Featured Images in Genesis using 3 lines of code
    • Workflow loop through SharePoint list
  • Products
    • Basket
  • Enquiry

How to filter a SharePoint Person Column in Microsoft Power Automate

30th October 2021 By Maciek Grischke Leave a Comment

If you ever wondered how to filter through a SharePoint Person or Group column in Power Automate Get Items action, you’re in the right place.

The scenario is this. You have a list of Employees, and the Person (only) column is called Employee. To filter through that column you need to choose the value you want to filter on since the Person column is a lookup column and contains more than one value.

So if you want to filter on the person’s name i.e., DisplayName the following is the query you need to use:

Empployee/Title eq ‘John Smith’

What I recently learned by using Power BI Is when you try to expand on the Person column, you will see what “attributes” are available. DisplayName is not one of them, but the Title is. The person column is a hidden user list in SharePoint that is synced with Office 365 users list, whether your users are Office 365 only or synced with on-prem Active Directory. The system between Office 365 and SharePoint is happening in the background and we have no control over it.

The available attributes are: (case sensitive)

  • Title (Name, Full Name or Display Name)
  • Name (Claims, i.e., i:0#.f|membership|username@company.com)
  • EMail (email address)
  • MobilePhone
  • Notes
  • Department
  • JobTitle
  • FirstName
  • LastName
  • WorkPhone
  • UserName (this is the UPN where in some cases could be slightly different than the company email address. For example, the way I do this is that I create username like this: SurnameN@company.com as the UPN and Name.Surname@company.com as the offical email. Of course both will work as an email address. Following this logic introduces an additional security layer for users that fall a victim of phishing where their official email address often gets pre-populated on dodgy fake-login-websites. The user enters their password, but the actual username is not their email so it’s confusing to scammers and they neger get to “hack” into their accounts)
  • many more are available and if you want to see them all, go to Power BI, connect to a SharePoint list, open the list (data source) in Power Query Editor and expand the person column to see all available atrrubites. You can do the same in Excel. Create a new spreadsheet, in Data click Get Data, select Sharepoint online, select the list, open the list in Power Query Editor and viola.
You can view the hidden user list in SharePoint, but it doesn’t give you what columns or attributes are actually available. To see the list, add /_catalogs/users/simple.aspx to your root SharePoint URL, like this: https://company.sharepoint.com/_catalogs/users/simple.aspx

Filed Under: Tutorials

Reader Interactions

Leave a Reply Cancel reply

You must be logged in to post a comment.

Primary Sidebar

  • Lock and Unlock your Jaguar with Siri
    By Maciek Grischke
    21st January 2023
  • Start Jaguar Engine Remotely with Siri
    By Maciek Grischke
    6th December 2022
  • Travel Mileage Claim Form in Power Apps using TomTom API
    By Maciek Grischke
    24th September 2022
  • Backup and restore IONOS DNS records using Power Automate, API, including SOA record
    By Maciek Grischke
    14th August 2022
  • Update the Picture column in SharePoint using PowerAutomate
    By Maciek Grischke
    16th November 2021
  • Home
  • About
  • Services
  • Blog
  • Products
  • Enquiry

 

Designed in-house by GRISCHKE SOLUTIONS © 2022 · Log in

Address

GRISCHKE SOLUTIONS
8 Sycamore Cottages
High Street
Pembury
TN2 4PJ

Site Info

Privacy Statement

Cookies Policy