Unanswered

Building Flow Using Power BI Results Table

(0) Share

Report

Posted on by BrewerHaHa

Hi everyone, I am trying to create a power automate flow which will send out emails to various different store managers. The issue I'm having is that I want to send the store information to the manager of that store as well as their results but do not want to set up a separate flow for each store, I would like them all to run from a single flow.

My Power BI data looks like the following:

Date	Store Name	Item Code	Sales	Sales Target	Store Manager Name	Store Manager Email	Staff Employed
01/01/2024	City A	10001	6	7	April	April@zzz.com	5
01/01/2024	City A	10002	9	4	May	May@zzz.com	5
01/01/2024	City A	10003	4	5	Leo	Leo@zzz.com	5
01/01/2024	City B	10001	2	2	Mikey	Mikey@zzz.com	3
01/01/2024	City B	10002	0	4	Don	Don@zzz.com	3
01/01/2024	City C	10002	5	7	Raph	Raph@zzz.com	2
01/01/2024	City C	10003	9	9	June	June@zzz.com	2

For store A, I would like to send the following results:

Date	Store Name	Item Code	Sales
01/01/2024	City A	10001	6
01/01/2024	City A	10002	9
01/01/2024	City A	10003	4

Since I am using Power BI, the flow I currently have looks like the below. I have no issue bringing in the table for each store but wish I could run a loop. That way I can get the email address from the data stored in Power BI and then bring it into the 'Send an email' portion of the flow. I would also like the data to filter to that store so City A's store only gets their results. Is this possible or is it too much?

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

v-yueyun-msft on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi , @BrewerHaHa

Do you mean you want to send email to "April@zzz.com;May@zzz.com;Leo@zzz.com" for this html table:

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
BrewerHaHa 62 on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Yes that is correct, apologies, I will update the post now
If we can send emails to even 1 of the managers, that will be good.

Was this reply helpful? Yes No
Verified answer

v-yueyun-msft on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi , @BrewerHaHa

Thanks for your response! This is my test data in Power BI and the return for the "Run a query against a dataset" in flow:

This is my test flow:

(1)The select action:

item()?['Table[Store Name]']

(2)The Apply to each action:

The union() function is used to get the no duplicate store name lists.

union(body('Select'),body('Select'))

item()?['Table[Date]'] ===== item()?['Your field Name']

join(xpath( xml(json(concat('{"root":{"item":',body('Filter_array'),'}}'))) , '/root/item/Table_x005B_Store_x0020_Manager_x0020_Email_x005D_
/text()'),';')

This expression is used to get the all the managers in one store.

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
BrewerHaHa 62 on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi @v-yueyun-msft

Thank you for your detailed response. I'm very new to Power Automate. Where do I get the return for the "Run a query against a dataset" in flow? Is this from the Paramaters -> Outputs -> Body section that you can get to once the flow has been run?

Also my data is sent on a schedule, so it may show the data from last week dynamically. Will this option still work?

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi , @BrewerHaHa

You can see the run flow history here:

If your flow is triggered on a scheduled basis, the stream will send the data that was retrieved at the time of the trigger.

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
BrewerHaHa 62 on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

@v-yueyun-msft Thanks so much for your assistance. I will check whether or not I am able to implement this solution tomorrow. I will update this thread as I go along.

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi, @BrewerHaHa

Thanks for your response! Of course, you can test it on your side now. If you have problem, you can send me in this case.

The next two days are weekends, and I will get back to you as soon as possible in Monday.

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
BrewerHaHa 62 on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi @v-yueyun-msft
I just tried this and was unable to manage to get this working but I can tell it's very close!
I could not do exactly what you did so I had to make some changes to see if I could get the flow to run. When I tried to run it, I had 2 issues.
a) My first issue is was in the 'Apply to each' step. In this step I got an error saying 'ActionFailed. An action failed. No dependant actions succeeded.'
I think this is because I didn't implement the code here which is
union(body('Select'),body('Select'))
item()?['Table[Date]'] ===== item()?['Your field Name']
Does the second line with the date table also go into this step? If it does, which column do I use for 'Your field name'? If it does not go to this step, does it go somewhere else? I put a screenshot below of what I currently have in this section to hopefully help.
b) I also had an error in the 'Create HTML table' step. I knew that it would not go well because when I clicked on it and tried to find 'Body' as an option, it was not available. I tried to use 'First table rows' instead but this did not work when I tried to test the flow. Is the reason I cannot see 'Body' as one of the options because of the 'Apply to each' part of the flow being incorrect? I have another screenshot below and the error given to me is:
'InvalidTemplate. Unable to process template language expressions in action 'Create_HTML_table' inputs at line '0' and column '0': 'The template language expression 'outputs('Filter_array')['body/firstTableRows']' cannot be evaluated because property 'body/firstTableRows' doesn't exist, available properties are 'body'. Please see https://aka.ms/logicexpressions for usage details.'.'

Was this reply helpful? Yes No
BrewerHaHa 62 on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

@v-yueyun-msft
I also just tried this
union(body('Select'),body('Select'))
item()?['Date Table[Date]'] ===== item()?['Test Table[Date File Loaded]']
in the 'Apply to each' step but I am getting the error 'The input parameter(s) of operation 'Apply_to_each' contains invalid expression(s). Fix invalid expression(s) for the input parameter(s) of operation 'Apply_to_each'.' when I try and save this flow
I am guessing that this is incorrect. Is this code
item()?['Table[Date]'] ===== item()?['Your field Name']
meant for the step 'Create HTML table'?

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

Re: Building Flow Using Power BI Results Table

Hi, @BrewerHaHa

Thanks for your quick response! For your question, this is my understand:

(a) The "union(body('Select'),body('Select'))" code has no error. And this is put it in the Apply to each :

And the "item()?['Table[Date]']" ===== item()?['Your field Name'] code is put in the "Create HTML Table" action:

This is used to custom the Html table column name and values.

The "Your field Name" you can find in your flow run history in the output of your "Run a query against a dataset" action.

(b) The Apply to each action return error code means one of the action in its return error. You need to check if your "Filter array" return successfully which is used to filter the array by the Store Name:

And you said that your "Create Html table" return error code (property 'body/firstTableRows' doesn't exist), i recommend you to change to the old designer:

This error code is your "outputs('Filter_array')['body/firstTableRows']" code is wrong, you need to use the body('Filter_array')

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No