“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.
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]
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.
Hello and thank you for the excellent job done on this website,
I have managed to follow your lead, but I wonder if I could go further ?
in the end of your xpath expression the “?[0]” makes the focus on the first row of the array.
I would like to multiply the rows in table A with as many occurence in Table B, the equivalent of a left join.
I know I can do it with a loop, but it is precisely not the point of your tutorial 😉
thanks a lot for your amazing job and help
Hello Chaby,
the solution expects 1:1 relationship, hence the 0. If it’s more than 1 I guess you could skip the [0] and take the whole array of outputs from the xpath(…) and join(…) them to a single string.
Is it possible to do this with four tables?
I have a CLIENT, with an INVESTMENT PRODUCT, with an INVESTMENT PRODUCT DEAL, with an UNDERLYING INVESTMENT.
All linked one to many from the top down.
In my Power Automate flow, I want to send an email with information from all four tables.
Hello Hilda,
if the tables are connected, you can extract the values in an easier way as explained here: https://tomriha.com/lookup-data-from-connected-dataverse-table-with-power-automate/, although I never did it more than 1 level deep so not sure how to get to the 2nd and 3rd level of the tables. Maybe you’ll have to do the ‘Select’ with xpath multiple times to connect the values from each table separately.