Flow to send emails with data from multiple PowerBI tables

[dodonald]

I have a list of system X users who experienced a job change (e.g. have a new title) in PowerBI. I then have a table with any documents those users own and a separate list of open tasks those users need to complete.

For example:

Table 1:

User ID	Document ID	Document Name
1	10	SOP 10
1	20	SOP 20
2	30	SOP 30
4	40	SOP 40

Table 2:

User ID	Open Task #	Task Name
2	50	ABC
3	60	DEF
5	70	GHI
6	80	JKL

I want to send an email to each user with a list of Document and/or Tasks they have in the system. I've followed this video in the past and can successfully send an email based on all users in a single table. I also see this solution to create a flow with data from 2 different tables.

Where I'm getting stuck is the User IDs are different between the 2 tables. If I loop through each from 1 table, I would miss those that are only in the other table. How can I loop through ALL possible IDs and pull info from both tables?

Thanks in advance!

[ManishSolanki]

Hi @dodonald 

You can use select action with xml & xpath function to merge both arrays. Here is the sample.

I have used compose actions to store the input arrays:

[
{
"User ID": "1",
"Document ID": "10",
"Document Name": "SOP 10"
},
{
"User ID": "2",
"Document ID": "20",
"Document Name": "SOP 20"
},
{
"User ID": "3",
"Document ID": "30",
"Document Name": "SOP 30"
},
{
"User ID": "4",
"Document ID": "40",
"Document Name": "SOP 40"
}
]

[
{
"User ID": "2",
"Open Task #": "50",
"Document Name": "ABC"
},
{
"User ID": "3",
"Open Task #": "60",
"Task Name": "DEF"
},
{
"User ID": "5",
"Open Task #": "70",
"Task Name": "GHI"
},
{
"User ID": "6",
"Open Task #": "80",
"Task Name": "JKL"
}
]

Next, we will use select action to create a list of user id from the input array. Pass the output of compose action as its input:

Click "Switch Map to text mode" button:

Enter the below expression:

item()?['User ID']

Similarly, add another "Select" action to get id of users from compose 2 action:

item()?['User ID']

Finally, to merge both array using "Select" action. To get unique user id from both array, we will use union() function in the form of expression:

Use the plain text in the Map/key column and add expression in the value textbox:

key	value
User ID	item()
Document ID	xpath(xml(json(concat('{"root":{"items":',outputs('Compose'),'}}'))),concat('string(//User_x0020_ID[text()=','''',item(),'''',']/../Document_x0020_ID/text())'))
Document Name	xpath(xml(json(concat('{"root":{"items":',outputs('Compose'),'}}'))),concat('string(//User_x0020_ID[text()=','''',item(),'''',']/../Document_x0020_Name/text())'))
Open Task #	xpath(xml(json(concat('{"root":{"items":',outputs('Compose_2'),'}}'))),concat('string(//User_x0020_ID[text()=','''',item(),'''',']/../Open_x0020_Task_x0020__x0023_/text())'))
Task Name	xpath(xml(json(concat('{"root":{"items":',outputs('Compose_2'),'}}'))),concat('string(//User_x0020_ID[text()=','''',item(),'''',']/../Task_x0020_Name/text())'))

Output:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[dodonald]

This is definitely helpful! But how do I do the first parts when the data is in PowerBI. In reality there are 5 columns of data in 1 table and 10 in the 2nd. And between them there could be several thousand rows depending on the week.
For example, this is what I have to send emails from *1* powerbi table.

How do I modify this to pull in both tables? Do I do the "Run a query against a dataset" and "Parse JSON" twice before the steps you have above?