“I need email addresses of user(s) in various Dataverse columns for my Power Automate flow, do I have to extract each of them separately?”
If you’re building solutions over Dataverse, you probably notice that it behaves a bit differently when it comes to people picker columns. Unlike the SharePoint based solutions, the users are not taken from Entra ID (formerly known as Azure AD). In Dataverse they’re stored in the User table and every lookup is a link to that table. The only information you get directly is the user display name and id in the User table, which is often not enough. Email is the unique user identifier across all applications, the one you often need. To send emails, assign tasks, to identify the user within the M365 platform…
What’s the most efficient way to get it? To list all the user emails in a single action, without the need to search the User table multiple times?
Use the Expand query
As explained in the previous article on lookup columns, Power Automate gives you the option to send complex queries to Dataverse. One of them is the Expand Query where you can extract data from all connected tables right away. And since people picker columns are just lookups in Dataverse, you can apply the same principle here.
For example, to get email of the OwningUser of a row.
owninguser($select=internalemailaddress)You can get email for multiple users if you add more queries separated by a comma.
owninguser($select=internalemailaddress),cr09b_User1($select=internalemailaddress),cr09b_User2($select=internalemailaddress),cr09b_User3($select=internalemailaddress)
Go even deeper with the Expand query
But it doesn’t end there, you can go deeper! Not only to the directly connected table, but to the table connected to that table too.
Main table Lookup -> 1st level table Lookup -> 2nd level tableFor example, my demo table has lookup via RowLookup column to RowLookup table, and the RowLookup table has the owning user column whose email I want.
So far we used only the $select parametr to select specific column as the expand part was covered by the action itself. But if you want to go deeper, to the next connected table, you must add the $expand parameter too, e.g.
cr09b_RowLookup($select=cr09b_name;$expand=owninguser($select=internalemailaddress))
The syntax it the same as before. Only this time you’re referencing the column in the 1 level deeper table and add the ‘$expand=’ parameter. In the example above I use:
- cr09b_RowLookup lookup column that’s connected to the 1st level table
- $select to select the cr09b_name column from that table, just to reduce number of properties
- $expand to get the corresponding entry in the 2nd level table based on value in the lookup column
- inner $select to get the internalemailaddress from the 2nd level table
The result is then part of the output JSON from the action. It won’t appear in among the dynamic contents in the designer, but you can extract it directly from the JSON.
Summary
As you can see, you don’t need that many actions to collect data from across Dataverse. Power Automate dataverse actions allow you to expand the query to connected tables too, an easy way e.g. to extract email address of a user. But not only that, you can select any data from rows connected via lookup columns, let it be directly in the next table on in the next ones. A single action that can give you all the related data in a single step.