Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to JOIN two Dataverse tables in Power Automate

Posted on November 23, 2022November 27, 2022 by Tom

“I have two Dataverse tables I need to join together, is there a better way join the tables in Power Automate than in a loop?”


If you worked with SQL databases in the past, you probably know how simple it is to join two tables. But what about Dataverse? Is there some easy way to connect two unrelated tables within Power Automate flow? Without a lot of loops and a very slow flow?

Merging the tables

Joining two tables is not different from merging two arrays – tables are just two arrays with rows after all. You need a shared value in both of the tables, for this example let’s use a lookup column (and forget for a while that the tables are actually connected). One table has the lookup value = row id, the other table has the actual row with this id. Knowing this there’s a good article by Paul Murana on merging arrays efficiently.

To apply this approach on Dataverse tables:

Firstly, list rows in both of the tables.

Secondly, use ‘Select’ to select the relevant columns in the second table. This step is necessary for the flow efficiency.

Thirdly, enclose the output array in additional elements and turn it into XML.

Power Automate join dataverse tables

And once you have an XML you can use the xpath(…) expression, in this situation in another ‘Select’ action. I prefer to use ‘Select’ as the standard ‘List rows’ action gives you too many columns when you often need only some of them.

Select the values from the first table, and for the values from the second table use an xpath(…) expression as below.

xpath
(
  outputs('XML'), 
  concat('//Array[<UniqueIDFromSelect>/text()="', <LookupColumn Value>, '"]/<ColumnNameFromSelect>/text()')
)?[0]

Note: < … > are placeholders, replace them including the < and >!

For my tables it’ll look as below:

xpath
(
  outputs('XML'), 
  concat('//Array[GUID/text()="', item()?['_cr09b_lookupcolumn_value'], '"]/AnotherLookupColumn/text()')
)?[0]
Power Automate join dataverse tables

And that’s it, you just selected data from two Dataverse tables and turned them into a single array of objects.

Summary

When merging two Dataverse tables, Power Automate gives you multiple options how to join them. You can do it row by row, using an ‘Apply to each’, and wait forever for the flow to finish, or you can use a bit of code. With a few actions to prepare the data you can use one ‘Select’ with the xpath(…) expression that’ll put it all together.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes