I made an UPDATE to this video with styling and much different technique that I feel works better for large datasets - please see this on, particularly if you’re more familiar with DAX! th-cam.com/video/8YnBotHynD0/w-d-xo.html For those asking how to format the columns in the table, you can either use DAX FORMAT() in Power BI or use the technique you see Reza using here with "format by example" on the columns on the Power Automate side. He also mentions how to style the table in a super-friendly way right after formatting!: th-cam.com/video/NipyM8UKG6U/w-d-xo.htmlsi=Tge6sSuaU6ru_1ar&t=630
Thank you Christine! I just used this to create a fancy email for supervisors about employees with upcoming service anniversaries that our HR department has been doing manually for ages. They are very happy thank you!
For those of you who have Premium capacity/Fabric, this feature just came out which is similar conceptually but much more robust! powerbi.microsoft.com/en-us/blog/dynamic-subscriptions-are-now-available-for-power-bi-reports/
Your videos are very helpful. With these new features can we send email with HTML data similar to above? If you get a chance can you do share the video with this new feature.
Ironically I just recorded a video on exactly this yesterday, working on editing it right now - should be ready Tuesday. 😊 The technique is much better that this one imo so I think I am going to unlist this version to avoid confusion (FYI in case you try to get to the comment and get an error!)
Thank you I really just needed a way to figure out how to get the PowerBI data table I was wanting from. Run a query against a dataset AND Parse JSON sections did the trick!
Please i kept on getting this error below Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '4536' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'.
This is exactly what I need and it works!!! Just ensure to switch to classic Power Automate if you’re using New designer UI as the new UI generates different schema
Kindly help, I have created this workflow to run every week, since the data on the visuals changes every week, however every week its picking the same data , that was generated when the flow was created, even though the data has refreshed on the visuals. What am i doing wrong?
Is your query using date filters at all if you look at the query DAX in the flow? If you had a date slicer on the page, it might be even if you didn’t mean it to be. Make sure you’re using relative dates (eg from a date table) and that you’re querying a dataset that is refreshing-
Thank you, yes i have filter that says date should be in the last week.. Is that causing the problem.I want the visual to only show data of the last 1 week only
The sort is normally done as part of the query step - so if you are using the performance analyzer to pull the DAX, just sort your visual on what you want it sorted by and it'll become part of the query to copy. If you're hand-writing it, you can put the order-by as part of your DAX.
I have applied exactely the same but still getting this error when I press save: "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '5701' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions." Could you please help?
Premium capacity and paginated reports :| -- that's kind of the way things go in M365, if you have enterprise-scale things you need to do, it usually requires a licensing bump.
Hi Cristina, your videos are really helpful, thank you so much. However, at the 4:00 minute mark, I think need unselected the circle buttons for S, T, W, T, F, and S should be gray, but Monday should be blue. This is needed if we want to select only once a week, every Monday
By the way, the “run a query against a dataset” return limit is 500 rows. Didn’t find this out until just now or I would have mentioned it in the video!
One way to resolve this is, once you copy the query from the Performance Analyzer, look for the *TOPN(* function in the query. It will be limited to 501 by default. I've increased it to larger values and it worked
For some reason I do not get JSON tags to select from @8:45 although I see "Parse JSON" step to finish without errors. Have they changed something substantially in Power Automate during the past year so that this example no longer works as expected ?
That is essentially the purpose of the parse JSON action, so I don’t think they would remove it. Is there an output of the step? It could be running but outputting blank for some reason. Also try the classic UI if you’re in modern, some things don’t show up in modern.
Is there a limit to how many columns you can have in your Power BI table for the "run a query against a dataset" to output "First table rows"? Around 6:58 of your video, you said to copy that output but every time I have more than 6 columns in my table, the output for the "run a query against a dataset" returns a different value. It instead has a link that says "Click to download". Thank you for the video by the way!
Yeah, I do think there are limits, potentially on both rows and columns on the return. I can’t seem to actually find documentation on that anywhere though and it definitely isn’t obvious (you’d think if you hit the limit they’d give you a message… but that would be too convenient). This thread seems to indicate that you can set a batch size to address it - let me know if that works? I’m curious! powerusers.microsoft.com/t5/Using-Connectors/Run-a-query-against-a-dataset-doesn-t-show-all-data/td-p/1700884
Hello Christine, I followed all the steps to the T, however instead of sending clubbed emails for the common distinct email addresses, its sending the same data twice (multiple) to the email address with the same data, though the HTML is clubbing both the data for the same email address, but the emails are going twice if the same person email is there on 2 rows... Please help, am just stuck on this now.
Check the output of the step that unions the array to make the values distinct in the flow history. Are they distinct there? If not there might be an issue with that step. Make sure you’re looping on that output and that the recipient is also the right value card. The flow history is going to be where you figure out where something is wrong-
Hello Christine how do I create a flow to send emails from a paginated report using a sharepoint list as a variable input for agency emails to the Paginate Report, but only send the report if the agency is on the list of parameters in the paginated report?
Hi, have you tried this feature yet? powerbi.microsoft.com/en-us/blog/dynamic-subscriptions-for-paginated-reports-preview/ For paginated reports, I would stick with the built-in subscriptions if possible since they have all kinds of functionality there. There’s an option in recipients config to use values in your dataset, so you could potentially pull the SP list into the dataset and use it that way.
It somewhat depends on what you’re trying to do, so if you’re trying to filter on “last 10 days” you’d use a date table with a relative date column (sometimes called an offset). If you want a user to input a value when the flow runs, you can use a manual trigger flow and stick that input variable in the place of a given filter value in the DAX.
any thoughts on how you would approach having multiple recipients in the email column? I have a use case where this would be perfect, but I want to send it to the users in the column delimited by a semicolon.
Not sure the context, but if you’re looping over user emails, you can combine them with a semicolon between using “append to string variable” and inserting the email and a semicolon character in the input-
@@bi-omeI guess to give you some context I have a report in Power BI with for work orders. There is a column that we use for notifications named "Contact" and it is in email format. The thing is there are multiple people in each record and the column contains the email of the person doing the work, their manager, and the location manager. Is there a way in Power Automate to email all three of those people with all of their work orders they have due. Hope that helps.
@@elvir24 Yes, I think the easiest way to do this would be to split the values into new rows in a "Contact" dimension table - that would be recommended even if you weren't trying to email just to be able to get those values in the Power BI report as a filter (you then relate the two tables on the row ID). Then you would add the contact email column to your visual you're getting the dataset query for (there would be one row per contact). I did something similar with the expanding to new rows for a "stakeholders" field in this video, except instead of using the "expand" in Power Query, in your case you would right-click the column and use "split by delimiter" "to new rows": th-cam.com/video/LYu3wqb2Nx4/w-d-xo.htmlsi=B-MZLlomfUHAShQH&t=327 Does that kind of make sense? You could probably split the field in Power Automate as well, but this is one of those cases where if you're working with a Power BI report on the other end it's more valuable to do it there because then you can use it there as well. :)
@@bi-ome yes, I see where you are going with this. Luckily I am already pulling in the contacts through a dimension table and then I just concatenated the three contacts into a column and used them in the report as one column instead of three. Stakeholder preference. I am going to give that approach a shot right after I figure out how to get around a time out error with the "append to variable" step. I have about 1800 work orders and as it is looping through it is erroring out due to a time limit that I never knew existed. :(
@@elvir24 That’s fine! The table visual with the expanded rows is just temporary to grab the query, you don’t have to keep it in the report afterwards. Matt Devaney did a blog post recently about optimizing for speed, you might check that out - this other one was a really good video for it too that hasn’t hit the mainstream (it’s a recording from a user group presentation): th-cam.com/video/1uoyav_oI1U/w-d-xo.htmlsi=9N5DQzPkTBqKxiY8
Hi Christine, this seems like exactly what I will need but when I plug in the DAX query and test it, the output is just [], which obviously is hard to parse as a JSON! Any idea why that might be? The dataset updates daily and should be pulling 8 columns and two rows today. Is there some security level on the PBI side I should be aware of?
It sounds like your query is returning blank - does the account that's running the DAX in Power Automate have permission to view the report? I would double check that the DAX is actually evaluating
@@bi-ome It is all through my account. I fiddled with the filters on the visual in the end and got it to work. No idea why it wasn't working before but happy it seems to be working now! Thanks so much!
Awesome content.. Followed all steps but unable to save the last step due to a connection issue. It's erroring out as "Some of the connections are not authorized yet. If you just created a workflow from a template, please add the authorized connections to your workflow before saving". But when i check connections, they are all connected, up and running. Any thoughts??
Check the ellipsis menu for each of the major action steps (e.g. the get dataset query, the email) - there should be a connection section in each of those that tells you which connection the step is using (they can each use a different connection potentially). Make sure it's a connection without an error icon next to it and if it has an error icon, change it to a different connection or add a connection. My guess is the culprit is the "send an email" action, that one can behave differently depending on who you are trying to send the email "as" (with the send an email, I like to use a shared mailbox to send - I have another video on how to set that up if you want to go that route), but you also see this often when you try to change ownership of a flow.
Hi Christine! Thanks for the vid! One problem i have is when the table gets updated weekly, the emails still send to the recipients in the previous version of the visual no matter how many times i publish. How can I solve this? Do I have to update the Performance analyzer part every time?
Do you have refresh scheduled on your Power BI dataset, and is it running? It should re-run the query on the schedule, and the query itself isn’t looking for specific names, so I think perhaps it’s the data itself that isn’t updating.
@bi-ome due to IT reasons, we dont have a gateway established but I'll reach out and see. Shouldn't it work if i just update dataset manually, publish, then click the button(trigger)? Apologies if I'm not understanding your comment.
@@21randyo Yes, you can do that. 99% of Power BI developers are not willing to manually refresh data like that though, so I made assumptions! If your datasource is in the cloud, you can schedule without a gateway - not sure what yours is, but I often see people using file sources on desktop that could be in SharePoint with scheduled refresh without a gateway.
@bi-ome well, I'd like to one day call myself a Power BI developer so I'll look into it 😅 On the cloud, i have a workbook with links to files in the folder that are received daily. Those links are refreshed manually because i wasnt able to set up scheduled refresh. Then, I manually update and publish Power BI.
@@21randyo if your files are in the SP Online, you don’t need a gateway to schedule refresh! I have another video on that in my channel if it helps. If you make things in PBI you are a developer imo!
Thanks it was very helpufull, But I have a question How can I send the email to the people that are in the table each for his/her rows based on the email column?
That’s what this tutorial does - it get the distinct users and then filters the array of data on them to create an email for each user with their own data.
Yeah, you can cut out the majority of the “rows” in the part that has the data, it only needs a small sample. Just make sure the last data row doesn’t have a trailing comma so that the JSON is valid.
I'm trying to do a matrix but its proving way to hard to do so, do you have a video on your channel that make embeded matrix in the e-mail that i can watch to solve my issue?
No, a matrix would be really tricky to do with this. If you want to use fancier visuals, I would recommend using the paginated report subscriptions that will send a PDF or Excel file vs trying to code the HTML.
Hi Christine, great video, I just have a question I have same information but I wanted to add besides the account owner name and email I wanted to add account managers and their emails so two more columns how would you add that to the flow? For example I want to send one email with the same info to two people.
Thanks! Sure, in my opinion the easiest way to do this is to just add that column to your table, copy the query, then make a copy of the Power Automate flow (there's a "save as" option in flow properties) and replace any of the account owner steps with account manager. You'd end up with two scheduled flows, one for each. You would have to duplicate some of the steps either way because you're looping on a specific set of column values, so this just makes it easier to manage imo. It's not like these things have a cost per flow, but then I'm biased towards "keep it simple"...
@@bi-omeHi Christine Thanks 🙏🙏 for all. My questions I have 4 slicer or filter on my report store, district,region, and territory generally I have 100 stores but when the report refreshed I got only 40 or 50 stores and their territory, The regional, district, managers should get an email. Do I have to run a query against the data set. And how much data flow I have to have? Any recommendations?
@@samueldemise9895 Hi, not sure what you're asking. If you want the email to only send to specific people from the dataset in an automated way then yeah you could use the "query a dataset" to do that. Another way to do report subscriptions is to use row-level security so that people can only see their own data and just subscribe everyone with the built-in subscription mechanism. The tutorial here is just to embed text data in the email; something that the built-in subscription doesn't do.
Yeah, CSV is a lot easier though - it's usually "close enough" since it opens in Excel. If you want to do .xlsx it'd probably involve creating the file and then adding the rows in a loop.
@@bi-ome Question, I managed to get extract for myself (Email and .csv), for my understanding, how to get different data from the visual? I mean, I changed in Service the filters and when I try to trigger Power Automate, it still gets me old data (my test data). Hows actually functioning with DAX Query? do I need to refresh the desktop version (and put a different filter) and then publish it every time, or it is enough from Service to choose what I need? Somehow Power Automate doesnt get this changes... p.s Do I need this table that is for export to have it in my report (with Power Automate visual) or?
Hi, when i try to send email, it send emails to all the email address that i have used as per the steps. Generally it should go to 1 person at times if an item has been added or modified in SharePoint
This flow is about using Power BI with Power Automate to send emails, if your source is a SharePoint list you probably want to just use the SharePoint triggers instead and not go through the steps in this video. But there is a for-each loop here that is looping over the recipients and sending an email per, so if yours isn't looping properly perhaps there is an issue with either the loop or the thing you're looping over? If your recipients are in a string instead of an array that could cause this behavior.
@@bi-ome so i have Power BI and a SharePoint point, i am using a Power App (canvas) to create an item in SharePoint. So whenever there is an update in SharePoint list i need an update, i am using a trigger " The flow is as below. When an item is created (SP)>Run a query against dataset(PBI)>ParseJSON>InitializeVariable>AppendArrayVariable>Compose>SendEmail So whenever the flow runs it send email to all the entries instead of the new/modified entry. It send 100 of emails to everyone whose email id is there in the list, i want the flow to send email to just the new entry.
This is a great video but i am getting stuck on the parse json. Power automate gives me a huge output and when i just use the few rows as example for schema i am getting an error due to some fields are empty or null. Checked the input and on some json rows its missing the field. Am i doing something wrong?
The output from sample tries to "guess" information about your data based on the data it has to work with. For the required fields, there's a section where it sets those (it's at the very bottom off the top of my head?). To adjust it, you just remove the fields you don't want to be required from the list of required fields in that JSON section.
Great video! I'm getting an error on the Parse JSON part "Invalid Type. Expected Object but got Array". In the first test, my output is a file to download. Any thoughts on how to solve this?
Yeah, I think I recall actually getting the same error while putting this video together and having to do a re-take when I didn't correctly copy the "example" response from the test run - did you copy the entire output for the creating schema "from example"? You need the entire response, not just the inner nested part if I recall.
@jets3370- did you figure out the solution to this? I am having the same issue. My output is also a file to download instead of the "First table rows" output per the video. And when I copy that output, I get the same error when I copy this result to the Parse JSON. I thought at first it's because I have to many columns but when I reduced the columns it's giving me the same result.
@@CindySalvesen Figured something out...after the 'query dataset in PowerBI' , you can select the Apply for each step, First Rows as Table and add an Action 'Parse from JSON' where you select Current Item and copy and paste the downloaded file. From there pickup with the video. Disclaimer, I'm very new to this....I'm also getting 1,200 emails when I set this because it's looping around. I need to fix this.
This is a great tutorial on how to dynamically email employees their personal records. In my case, I have two employees associated with the same record (meaning I have 2 email columns in the dataset). How can I dynamically send a single email that is received by both employees with their shared records? I'm sure that's a loaded question lol but a nudge in the right direction would be extremely helpful. Thanks Christine!
Thanks! I would just add the second column to the table, update the query in the flow, and drop the second email address column in the CC on the email (or semicolon after the recipient). As long as there’s always someone in the first field, that should work.
@@bi-ome Thanks for the response! I think I understand what you are saying, but my problem is that the second email will not always be paired with the first email. For example, user 1 could be paired with user 2 on two orders, so one email total would be sent to both of them. Then user 1 could also be paired with user 3 on an order, so one email total would be sent to both of them. In total, only two emails should be sent.
@@TJKubiesa If you are trying to send them a record set, and the second email address field is not the same every time for a single email recipient 1, you would need to just send that second person a separate email with "their" records assuming don't want them to get some stuff that's not their own (just duplicate the table/copy query - can do a new flow or put it in the same flow).
Christine - thank you for making this; it's super helpful! However, on the first 'apply to each' where the emails are being appended to the array variable, I get the error: "The value cannot be null. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'." Is there a way to have power automate ignore the nulls in the data?
I'd just filter your initial DAX query to not include null emails. Ideally where you pull your query from in the Performance analyzer should be precisely the data you're wanting to send out, you can put all your filters on there. Or you could filter on the Power Automate side with filter array, but it's better to just not query what you don't need. BTW there is an update to this video here - it uses a different technique that I prefer, you might check this method out: th-cam.com/video/8YnBotHynD0/w-d-xo.htmlsi=X-In0Q1ctihNEnId
Try switching to the classic Power Automate UI - the menu option is in the ellipsis menu next to the "test" button in the toolbar. I've been getting a lot of reports of dynamic content options not showing up in the new UI that just rolled out.
You can enter the hard-coded expression for the current item - for me this is "items('Apply_to_each_2')" - it needs to be set as an expression if you do it this way,
@@faizanzamir961 You can also reference the current item by it's "code" as an expression - this will depend on what your step names are, for me it is items('Apply_to_each_2')
I tried your method but I keep getting error message The value cannot be null. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'. and I know I do not have any null values
The documentation says maximum of 100,000 rows or 1,000,000 values per query (whichever is hit first). The latter is a combination of rows and columns, so if you have a lot of columns you'll hit it sooner. If you have more than that, I have another technique that queries and gets one row per recipient instead (it creates the table as HTML on the Power BI side before sending to PA), which usually shrinks it substantially--
@@bi-ome i actually trying to load data from Power Bi to sharepoint list , but same method , runa querry against a dataset and then parse Json , Yes , as you say many columns, lets say 100 . I m gettting only 500 rows
Try to figure out where the data is dropping. Check your query response body first and make sure the data is in there; then check the filter step to make sure there is still data in it after the filter. You might have a bad filter that’s returning no results.
I'm getting stuck around 13:07 where I build my HTML table with custom columns. I only see my column names from Parse JSON, *not* Filter Array. Any idea how to fix?
@@bi-omeok got it. The problem was my sample data set was too large, which resulted in a file download in the Run a querry against a dataset. Once I got that down to show just a few rows, copy/pasting that text into the Parse JSON Generate from sample resolved my issues. I used this to create a flow that would email each manager a list of their employees with their employee's open tasks. I've been asked if I could do this same thing but send a separate email to the manager for each of their EMPLOYEES. (e.g. each manager might receive multiple emails). How would i modify this to accomplish that?
@@daronodonald9662 If I'm reading your question right, you should be able to use the exact structure that's in the demo, but instead of emailing the assignee, use the "get manager" action before the email to get the assignee's manager and have the email go to them instead. That will send the manager of each employee a list of that employee's outstanding tasks, one email per employee (because the for-each loop is looping over assignees).
Hi Christine. This is super helpful. I read your pinned comment below after seeing that while in my test data I have two rows for the same email address, only one row is being populated in my HTML table. I tried changing "first table rows" to "body," but received an error stating an array was expected, but an object was received. This is so close though! Any idea how I can get this particular Flow past the finish line? In this scenario I'd like to have one e-mail sent with a 2 row HTML table. ** Update - this works for me with "first table rows." I just needed to republish the .pbix with the additional rows I added to the table viz. after I started creating this Flow. Thanks for sharing!
Thank you so much! This is what I was looking for. I only got stuck while adding the owner's name to the email body. Does anyone have any idea? Thank you!
What wasn’t working there? Not sure if you’re getting it from your dataset or from the user profile, but you can use their email address for the “get user profile” action and get their name from there. Otherwise if it’s in the dataset you would have to use an expression to get the first value on the filtered data since it’s got multiple values in it-
Great video, Christine! It helped me a lot in creating something very similar. Do you have any suggestions on how I could also apply a filter or create an array to send one single email by region/location?
Great! It'd hard to say without knowing the shape of your data, but you can absolutely filter the dataset query using "Filter array" (here's the first article I found that explains how to filter - not affiliated with them at all: www.powertechtips.com/filter-array-power-automate/). You can add parallel branches to flows to do multiple things at one time - so for example filter the array by different values per branch, and have a different email for each (you can copy/paste your email action and edit values to make it faster). Parallel branch will pop up as an option when you click a node between actions.
Hi, this is an amazing video thanks.. i don't know if i am using a different version of power automate😂.. but when you are in the APPEND step i see that all the fields in 'Parse Json' are showed and you select the email field. In my case the section 'Parse Json' only have two options : Body and 'Body Items'.. I don't know how extract only the Email value. I am stuck😂😂
Thanks, I'm not sure I follow - you can't address email to a name, but for example if you have a list of which user ID rows need to go to which email address, you could for-each loop over that to reference it in the actions. Unless you mean you want to insert their name in the email body?
@@bi-ome yes we need to address the mail with their name, instead of id. But we are passing variable as userid, so we can get distinct user list.When we insert user name in mail, same user getting multiple mails, instead of unique mail.
@@ramyarangasamy3770 If you need to remove duplicate values, you can do the same Union() step we did on the emails on your user IDs - this gets a distinct list of IDs.
Is it possible to do the same for Paginated Reports in Power BI? We want to embed the table generated from the Paginated Report into the email body just like how it is being done in SSRS. Currently I can't find any other solutions for this using Power Automate, maybe someone can help with a similar approach from this video.
The thing with paginated is it's just a type of report - it uses a semantic model just like a normal report. You can query the dataset that the paginated report is referencing the same way to get a table with data that matches the paginated report, but you can't "get" the paginated table per se. The only paginated-specific action in Power Automate is to send it to a file, not embed it, but you could use that if attaching a file is acceptable.
Hi, thanks for the video. I am getting this error: Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '8878' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'. I have added the filter_array in my second Apply to each loop. And in the filter query, I added the array variable initialized. How can I fix this pleasee?
I’m not sure, it’s hard to say without seeing it. If you want you can send a screenshot to my email in the channel about page and I can try to help (if it has nothing sensitive in it)
Hi, Thx for this video. It works fine for me, but I'd like to send a CC as well. I have another column with the agent's email and I want them to receive a CC when their clients receive their related infos. How can I do that?
The "send an email v2" action has a space to add CC and BCC, you might need to open up advanced settings to see it. You should be able to use any data that's in your initial query, though depending on how your table is structured you might need to append emails to a semi-colon-separated string as a step if there's multiple.
I'm using the CC, but can't find what to put inside to make it work... It has to be part of the same loop, but info is coming from the clients' email. Sorry, I'm new to Power Update, but can't find any useful hints...
@@andrevachon9685 Yeah, so as long as the data in your table in PBI is structured the way you want, you should be able to use it. You do need to update the "query a dataset" and the "parse JSON" step to include your new table column to get it to show up. For how you shape that data, if you have duplicate values for the CCs you will want to use the same UNION() trick to remove duplicates on those values, OR just structure your original table so that there's one row per email, with a column for the recipient and a column for the CC (would likely require some DAX and some CONCATENATEX()). The thing with all these tools is there's lots of ways to do everything, so it's about picking the way with the least trouble for you.
Thx for not letting me down! I already did the same thing with the CC, so to have only one. I now have Get distincts emails and Get distincts CC. When I created the last loop you showed, I need to select the outputs from either one of them, right? So, I don't know what to do to get the outputs from the other one and to make sure the right CC goes according to the right report sent. I wish I could show you my flow...
You can’t currently embed the literal PBI visual in an email. You can attach a PDF of the report with paginated, but most of the paginated features are premium. That’s why we query it and put it in an HTML table here-
Great tutorial! How can I format the table to show money and date? The date is with hh:mm:ss and currency is with no $ mask. I tried to manipulate the variables with FormatDateTime(), but it is not possible to select/reference the fields inside the filterarray body().
Yes, you can use the DAX function FORMAT() inside the PBIX (not in the flow) to choose the formatting of the measure. This does go all the way through to the flow end of things, though I would do this in a new measure (vs one that is used in chart visuals) as I think the output of it is technically text.
Can anyone tell me how to do this with automate? Thanks! I have a power bi table with 7 columns and hundreds of rows: ‘code’ column, ‘description’ column, ‘quantity’ column, ‘total value’ column, ‘owners’ column, ‘manager’ column, and ‘functional managers’ column. I need to send the table by email to each owner, manager, and functional manager, but only with the rows relevant to them. The rows for owners, manager, and functional manager are populated with their email addresses. Please note that there can be more than one owner (multiple email addresses in the same row).
You can do one query for each audience - eg one for owners, one for managers, etc. How you query it depends on how the emails are structured, if there's multiple in a single field right now you need to split them to rows on the delimiter first in order to get distinct emails. Then you can basically follow the same pattern as the video. Alternately, if you have premium you can use RLS roles and paginated reports to send based on that.
Like the local user account on the machine? No, Power Automate Cloud has no info about the local machine, since it's the cloud tool. If you have data about which M365 users have which local usernames, or if they follow a precise format, you could either do a lookup on that or generate it though (e.g. orgname\first.last from the user profile).
This was great but I ended up sending myself "600" emails. I only have 3 "projects" and thought I amended the table to the project name and would only get 3 emails. Does anyone know which step may have an issue?
LOL, been there myself😅. It sounds like your filter array isn't filtering, or you're not looping on the filtered values. I am editing another video right now that handles the data structure differently, you might have better luck with that method - should be ready early next week.
Thank you!!! @@bi-ome I do have one other question if you have time and don't mind me asking. The Power BI report has a filter that looks for the latest report issued (from another source), I believe (but not 100%) this filter is getting pasted into the query and is possibly preventing my data from updating on my power automate email. Text from the query (filter part only) below: FILTER( KEEPFILTERS(VALUES('DailyLogManpower'[created_at])), AND( 'DailyLogManpower'[created_at] >= (DATE(2024, 2, 4) + TIME(18, 29, 2)), 'DailyLogManpower'[created_at]
Hello - thank you for the video it was helpful but I've run into a problem. I'm receiving the following error during the test phase: [{"code":"DetailsMessage","detail":{"type":1,"value":"The dataset '226s3700e2-2891' is a model hosted in Azure Analysis Services or SQL Server Analysis Services using On-Premise Gateway, which is not allowed."}} Any suggestions? Thank you
So I did an internet search on your error and this is the FIRST time in my life I have googled something and come up with only one single result - normally it's a million or nothing, lol! Basically, this action is not supported with the source you're using. You might look into what SSAS/AAS has available for built-in emailing of data, I believe it has something for that (e.g. see: learn.microsoft.com/en-us/sql/reporting-services/subscriptions/e-mail-delivery-in-reporting-services?view=sql-server-ver16 ). Or paginated reports.
how to use multiple filters in power automate and in my case there is a 10 fields to be used for creating HTML table is it possible to add those 10 fields not able to add those its limited to only 4 when we give show raw outputs. Please provide me solution for this?
There are limitations around the total amount of data returned by querying the dataset. There's some methods in this thread that might help: powerusers.microsoft.com/t5/Using-Connectors/Run-a-query-against-a-dataset-doesn-t-show-all-data/td-p/1700884
@@bi-ome We have 25 columns in the dataset to run . the input we are passing in run query in dataset is 25 columns but when we run in output we are getting only 4 columns.Can you please suggest possible ways to get all 25 columns in the mail.
Hi great tutorial. I have just one problem. I have employees attendance data in power bi and everytime I refresh it in power bi with relevant date, dax query and parse jason should also get updated automatically. But its not the case. How can I resolve this? Please guide
Hi Christine great video, can you do a power automate video that Exports Power bi Reports to CSV with the recurrence schedule being the trigger action. The report has a filter connected to a table where we get the data. This filter changes from time to time depending on the user's requirements ( start date - end date) end date changes. I want the automation to reflect these changes on the filter
Do you mean exports a table in the report or the entire report? I have a video here that creates CSVs on a schedule from a JSON response and stores them in SharePoint, if you take the query a dataset action and use that instead of the "get items" in the video, that should do it: th-cam.com/video/RBx-HbVpWTQ/w-d-xo.html. To get the start/end dates, you could add two queries to the flow get the min/max dates from your dataset (unsure what the requirement is here... you could put them in the filename?).
The requirements are as follows; Create an automated solution for exporting a Power BI report table to CSV format and then sending this CSV output via email to a designated recipient. Notably, the users who will be receiving the CSV output do not possess direct access to Power BI. Furthermore, it's essential to consider that the data presented in the Power BI report table is influenced by a slicer filter. Thus, any modifications made to the slicer filter by the user should result in corresponding changes within the automated CSV output. For instance, if a user specifies a particular date range in the report (e.g., Start date: 09/02/2020, End date: 08/03/2022), the data visible in the Power BI table should accurately match the data contained within the CSV output generated by the automation process@@bi-ome
@@bi-ome So far I have a flow that goes like Reccurence-scheduled (TriggerAction) > Run a Query Against a Dataset(PowerBi) > Create CSV table > Create Onedrive File(optional) > Send an Email v2(Outlook) Under the (Run a Query Against a Dataset) the DAX query I got from the performance analyzer is static, hence it does not reflect any changes made on the report after it's entered on power automate, so the CSV output won't match the report if a change is initiated from the filters
@@BAROS_Z Oh, yeah, I think the only way to get the user's applied changes is to use the Power Automate button visual or the Power Apps visual. The "query a dataset" is not going to be aware of what the user is doing in the report. I thought you meant that the source data was changing, which it would work for. The built-in subscription service lets the user sign up on a schedule that takes their applied changes - have you tried that? learn.microsoft.com/en-us/power-platform-release-plan/2022wave2/power-bi/subscribe-reports-filters-slicers-applied
@@bi-ome The data on power bi is connected to a live source, hence the use of the slicer filter. In this case The Power Automate Button works perfectly however the stakeholder are insists on not having a button. ( can you set restriction on who has access to the button?) Yes, I have tried the subscription service however some recipients to the report done have access to Power Bi. My mind is going in circles trying to accomplish this task.
Not that I know of, if you really want the pivoted matrix your best bet is the “analyze with Excel” that links to the model, or just the normal report subscriptions-
Hey, Christine Let's say I want to send the sales report to their owners only if each of its product price increases by 100. Can you please make video on this scenario?
All you’d need to do here is set up the visual you’re getting the DAX query from to reflect what you want. So in your table, put your account owner emails, product price change amount in whatever timeframe you’re after (as a CALCULATE() measure), and add a visual-level filter to the table for that measure > 100. Then copy that query from the optimizer and use it in the flow.
Also whenever i copy the dax query to power automate it only has hard coded date ,even if i filter it by past week. Any idea on how to resolve that? @@bi-ome
I am getting an error after i copy the query from the perfromance analyzer it {"error":{"code":"PowerBIFeatureDisabled","pbi.error":{"code":"PowerBIFeatureDisabled","parameters":{},"details":[{"code":"DetailsMessage","detail":{"type":1,"value":"'DatasetExecuteQueries' is not enabled for the tenant."}}],"exceptionCulprit":1}}}, how can this be fixed
The documentation is here: learn.microsoft.com/en-us/dax/format-function-dax If you have trouble, ChatGPT is quite good at writing you a statement for formatting if you give it an example and your column names.
im getting "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '4127' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'." error How do i resolve it?
I would take a look at your filter array step and make sure you have the correct thing inserted there. Here is an article on the error you're getting: powerusers.microsoft.com/t5/Building-Flows/Action-Apply-to-each-must-be-a-parent-foreach-scope-of-action/td-p/1266735
"Awesome tutorial! This is what I was looking for. Quick question: How can I include a card visual indicating Total Count in the email, being filtered by the email of the recipient? Thank you!
I would guess that you could do it in the exact same way. Getting the query should work the same for any visual type, I just happened to use a table. You could embed it in the email in a single-column table. If it needs to be fancy you’d need to do custom HTML.
You can repeat the actions on a different “query a dataset” to get another table below the steps for the first but above the email. You have to decide which is going to drive the “to” addressees for the email, but it’ll let you insert multiple tables in the body.
@@bi-ome Okay, Thanks! I know you mentioned it at the end of the video, but if you can provide any guidance at all as to how I can customize the table apperance in the email, that'd be wonderful! Any direction would be greatly appreciated.
I made an UPDATE to this video with styling and much different technique that I feel works better for large datasets - please see this on, particularly if you’re more familiar with DAX! th-cam.com/video/8YnBotHynD0/w-d-xo.html
For those asking how to format the columns in the table, you can either use DAX FORMAT() in Power BI or use the technique you see Reza using here with "format by example" on the columns on the Power Automate side. He also mentions how to style the table in a super-friendly way right after formatting!: th-cam.com/video/NipyM8UKG6U/w-d-xo.htmlsi=Tge6sSuaU6ru_1ar&t=630
Thank you Christine! I just used this to create a fancy email for supervisors about employees with upcoming service anniversaries that our HR department has been doing manually for ages. They are very happy thank you!
aw that's so great! GJ!
I can say "I love you" just saved me after a week of pressure to created a flow for CS team in my company.
Dear Christine, Thank you very much for making this video.
This is amazing. You're a rockstar for this.
Exactly what I needed - been searching on how to do this for ages. Great vid!!!
You're the real MVP Christine, thanks for the video. Helped me alot
For those of you who have Premium capacity/Fabric, this feature just came out which is similar conceptually but much more robust! powerbi.microsoft.com/en-us/blog/dynamic-subscriptions-are-now-available-for-power-bi-reports/
Your videos are very helpful. With these new features can we send email with HTML data similar to above? If you get a chance can you do share the video with this new feature.
Ironically I just recorded a video on exactly this yesterday, working on editing it right now - should be ready Tuesday. 😊 The technique is much better that this one imo so I think I am going to unlist this version to avoid confusion (FYI in case you try to get to the comment and get an error!)
Your detailed video helped me today achieve what I was looking for! Many thanks !
Thank you I really just needed a way to figure out how to get the PowerBI data table I was wanting from. Run a query against a dataset AND Parse JSON sections did the trick!
Sure! Glad it was helpful!!
I have no idea how this worked but it did, and it's amazing! Thank you so much!
🤣I commiserate, happens to all of us haha
Hello Christine! Thanks, it worked perfectly for me. I can now notify users individually! Thank you very much! : )
Please i kept on getting this error below
Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '4536' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'.
This is going to be very useful to me. Thank you very much for making this video 😍
So glad it was helpful!
This is exactly what I need and it works!!! Just ensure to switch to classic Power Automate if you’re using New designer UI as the new UI generates different schema
Totally! good tip 🙃
Kindly help, I have created this workflow to run every week, since the data on the visuals changes every week, however every week its picking the same data , that was generated when the flow was created, even though the data has refreshed on the visuals. What am i doing wrong?
Is your query using date filters at all if you look at the query DAX in the flow? If you had a date slicer on the page, it might be even if you didn’t mean it to be. Make sure you’re using relative dates (eg from a date table) and that you’re querying a dataset that is refreshing-
Thank you, yes i have filter that says date should be in the last week.. Is that causing the problem.I want the visual to only show data of the last 1 week only
This was exactly what I as looking for. Thanks!
Thank you so much !!! don't stop uploading content 👏💪
Love it! Great video.
Hi Christine. Thanks for the tutorial. Is there a way to sort one of the columns from the PowerBI Dataset in the HTML table?
The sort is normally done as part of the query step - so if you are using the performance analyzer to pull the DAX, just sort your visual on what you want it sorted by and it'll become part of the query to copy. If you're hand-writing it, you can put the order-by as part of your DAX.
Top tier walkthrough and narration. Thanks!
I have applied exactely the same but still getting this error when I press save: "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '5701' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions."
Could you please help?
Check you have the right apply to each "Current Item" selected. You will be selecitng the wrong one from the list
Thanks for your time/help! Also, saw your reddit acct in your Gantt video and realized you've helped me on there too 😭
Aw thanks haha!
thanks its useful, only concern is if you have more data query fails , any other option you may know to tackle that issue
Premium capacity and paginated reports :| -- that's kind of the way things go in M365, if you have enterprise-scale things you need to do, it usually requires a licensing bump.
Hi Cristina, your videos are really helpful, thank you so much. However, at the 4:00 minute mark, I think need unselected the circle buttons for S, T, W, T, F, and S should be gray, but Monday should be blue. This is needed if we want to select only once a week, every Monday
Good catch!
By the way, the “run a query against a dataset” return limit is 500 rows. Didn’t find this out until just now or I would have mentioned it in the video!
One way to resolve this is, once you copy the query from the Performance Analyzer, look for the *TOPN(* function in the query. It will be limited to 501 by default. I've increased it to larger values and it worked
For some reason I do not get JSON tags to select from @8:45 although I see "Parse JSON" step to finish without errors. Have they changed something substantially in Power Automate during the past year so that this example no longer works as expected ?
That is essentially the purpose of the parse JSON action, so I don’t think they would remove it. Is there an output of the step? It could be running but outputting blank for some reason. Also try the classic UI if you’re in modern, some things don’t show up in modern.
Is there a limit to how many columns you can have in your Power BI table for the "run a query against a dataset" to output "First table rows"? Around 6:58 of your video, you said to copy that output but every time I have more than 6 columns in my table, the output for the "run a query against a dataset" returns a different value. It instead has a link that says "Click to download". Thank you for the video by the way!
Yeah, I do think there are limits, potentially on both rows and columns on the return. I can’t seem to actually find documentation on that anywhere though and it definitely isn’t obvious (you’d think if you hit the limit they’d give you a message… but that would be too convenient). This thread seems to indicate that you can set a batch size to address it - let me know if that works? I’m curious! powerusers.microsoft.com/t5/Using-Connectors/Run-a-query-against-a-dataset-doesn-t-show-all-data/td-p/1700884
Thank you so much! You are the GOAT
Hello Christine, I followed all the steps to the T, however instead of sending clubbed emails for the common distinct email addresses, its sending the same data twice (multiple) to the email address with the same data, though the HTML is clubbing both the data for the same email address, but the emails are going twice if the same person email is there on 2 rows... Please help, am just stuck on this now.
Check the output of the step that unions the array to make the values distinct in the flow history. Are they distinct there? If not there might be an issue with that step. Make sure you’re looping on that output and that the recipient is also the right value card. The flow history is going to be where you figure out where something is wrong-
Hello Christine how do I create a flow to send emails from a paginated report using a sharepoint list as a variable input for agency emails to the Paginate Report, but only send the report if the agency is on the list of parameters in the paginated report?
Hi, have you tried this feature yet? powerbi.microsoft.com/en-us/blog/dynamic-subscriptions-for-paginated-reports-preview/
For paginated reports, I would stick with the built-in subscriptions if possible since they have all kinds of functionality there. There’s an option in recipients config to use values in your dataset, so you could potentially pull the SP list into the dataset and use it that way.
i have to keep date selection dynamic filter., any suggestion on how to keep dynamic DAX query
You are amazing! Thanks for this. Helped me out tremendously. Subscribed!
I have one question, suppose I wanted to add dynamic filter the table visual data in run a query against dataset then how can I implement.
It somewhat depends on what you’re trying to do, so if you’re trying to filter on “last 10 days” you’d use a date table with a relative date column (sometimes called an offset). If you want a user to input a value when the flow runs, you can use a manual trigger flow and stick that input variable in the place of a given filter value in the DAX.
thank you very much don't stop uploading content
any thoughts on how you would approach having multiple recipients in the email column? I have a use case where this would be perfect, but I want to send it to the users in the column delimited by a semicolon.
Not sure the context, but if you’re looping over user emails, you can combine them with a semicolon between using “append to string variable” and inserting the email and a semicolon character in the input-
@@bi-omeI guess to give you some context I have a report in Power BI with for work orders. There is a column that we use for notifications named "Contact" and it is in email format. The thing is there are multiple people in each record and the column contains the email of the person doing the work, their manager, and the location manager. Is there a way in Power Automate to email all three of those people with all of their work orders they have due. Hope that helps.
@@elvir24 Yes, I think the easiest way to do this would be to split the values into new rows in a "Contact" dimension table - that would be recommended even if you weren't trying to email just to be able to get those values in the Power BI report as a filter (you then relate the two tables on the row ID). Then you would add the contact email column to your visual you're getting the dataset query for (there would be one row per contact). I did something similar with the expanding to new rows for a "stakeholders" field in this video, except instead of using the "expand" in Power Query, in your case you would right-click the column and use "split by delimiter" "to new rows": th-cam.com/video/LYu3wqb2Nx4/w-d-xo.htmlsi=B-MZLlomfUHAShQH&t=327
Does that kind of make sense? You could probably split the field in Power Automate as well, but this is one of those cases where if you're working with a Power BI report on the other end it's more valuable to do it there because then you can use it there as well. :)
@@bi-ome yes, I see where you are going with this. Luckily I am already pulling in the contacts through a dimension table and then I just concatenated the three contacts into a column and used them in the report as one column instead of three. Stakeholder preference. I am going to give that approach a shot right after I figure out how to get around a time out error with the "append to variable" step. I have about 1800 work orders and as it is looping through it is erroring out due to a time limit that I never knew existed. :(
@@elvir24 That’s fine! The table visual with the expanded rows is just temporary to grab the query, you don’t have to keep it in the report afterwards. Matt Devaney did a blog post recently about optimizing for speed, you might check that out - this other one was a really good video for it too that hasn’t hit the mainstream (it’s a recording from a user group presentation): th-cam.com/video/1uoyav_oI1U/w-d-xo.htmlsi=9N5DQzPkTBqKxiY8
Hi Christine, this seems like exactly what I will need but when I plug in the DAX query and test it, the output is just [], which obviously is hard to parse as a JSON! Any idea why that might be? The dataset updates daily and should be pulling 8 columns and two rows today. Is there some security level on the PBI side I should be aware of?
It sounds like your query is returning blank - does the account that's running the DAX in Power Automate have permission to view the report? I would double check that the DAX is actually evaluating
@@bi-ome It is all through my account. I fiddled with the filters on the visual in the end and got it to work. No idea why it wasn't working before but happy it seems to be working now! Thanks so much!
Awesome content.. Followed all steps but unable to save the last step due to a connection issue. It's erroring out as "Some of the connections are not authorized yet. If you just created a workflow from a template, please add the authorized connections to your workflow before saving". But when i check connections, they are all connected, up and running. Any thoughts??
Check the ellipsis menu for each of the major action steps (e.g. the get dataset query, the email) - there should be a connection section in each of those that tells you which connection the step is using (they can each use a different connection potentially). Make sure it's a connection without an error icon next to it and if it has an error icon, change it to a different connection or add a connection. My guess is the culprit is the "send an email" action, that one can behave differently depending on who you are trying to send the email "as" (with the send an email, I like to use a shared mailbox to send - I have another video on how to set that up if you want to go that route), but you also see this often when you try to change ownership of a flow.
Hi Christine! Thanks for the vid! One problem i have is when the table gets updated weekly, the emails still send to the recipients in the previous version of the visual no matter how many times i publish. How can I solve this? Do I have to update the Performance analyzer part every time?
Do you have refresh scheduled on your Power BI dataset, and is it running? It should re-run the query on the schedule, and the query itself isn’t looking for specific names, so I think perhaps it’s the data itself that isn’t updating.
@bi-ome due to IT reasons, we dont have a gateway established but I'll reach out and see. Shouldn't it work if i just update dataset manually, publish, then click the button(trigger)? Apologies if I'm not understanding your comment.
@@21randyo Yes, you can do that. 99% of Power BI developers are not willing to manually refresh data like that though, so I made assumptions! If your datasource is in the cloud, you can schedule without a gateway - not sure what yours is, but I often see people using file sources on desktop that could be in SharePoint with scheduled refresh without a gateway.
@bi-ome well, I'd like to one day call myself a Power BI developer so I'll look into it 😅 On the cloud, i have a workbook with links to files in the folder that are received daily. Those links are refreshed manually because i wasnt able to set up scheduled refresh. Then, I manually update and publish Power BI.
@@21randyo if your files are in the SP Online, you don’t need a gateway to schedule refresh! I have another video on that in my channel if it helps. If you make things in PBI you are a developer imo!
Hi Christine, Great video :)
Thank you!!
Thanks it was very helpufull, But I have a question How can I send the email to the people that are in the table each for his/her rows based on the email column?
That’s what this tutorial does - it get the distinct users and then filters the array of data on them to create an email for each user with their own data.
Hello! when i try to copy and paste the schema in the parse json it crashes due to my output is very large i guess. Is there any workaround for this?
Yeah, you can cut out the majority of the “rows” in the part that has the data, it only needs a small sample. Just make sure the last data row doesn’t have a trailing comma so that the JSON is valid.
I'm trying to do a matrix but its proving way to hard to do so, do you have a video on your channel that make embeded matrix in the e-mail that i can watch to solve my issue?
No, a matrix would be really tricky to do with this. If you want to use fancier visuals, I would recommend using the paginated report subscriptions that will send a PDF or Excel file vs trying to code the HTML.
Nice video. It helped me step by step to achieve
Hi, Thanks for the video. How can I format a number to currency?
Thanks
You can use DAX "FORMAT()" to format as currency, there is an example in the documentation here: learn.microsoft.com/en-us/dax/format-function-dax
Thank you. worked :)@@bi-ome
Hi Christine, great video, I just have a question I have same information but I wanted to add besides the account owner name and email I wanted to add account managers and their emails so two more columns how would you add that to the flow? For example I want to send one email with the same info to two people.
Thanks! Sure, in my opinion the easiest way to do this is to just add that column to your table, copy the query, then make a copy of the Power Automate flow (there's a "save as" option in flow properties) and replace any of the account owner steps with account manager. You'd end up with two scheduled flows, one for each. You would have to duplicate some of the steps either way because you're looping on a specific set of column values, so this just makes it easier to manage imo. It's not like these things have a cost per flow, but then I'm biased towards "keep it simple"...
Great, thanks a lot Christine
@@bi-omeHi Christine Thanks 🙏🙏 for all. My questions I have 4 slicer or filter on my report store, district,region, and territory generally I have 100 stores but when the report refreshed I got only 40 or 50 stores and their territory, The regional, district, managers should get an email. Do I have to run a query against the data set. And how much data flow I have to have? Any recommendations?
@@samueldemise9895 Hi, not sure what you're asking. If you want the email to only send to specific people from the dataset in an automated way then yeah you could use the "query a dataset" to do that. Another way to do report subscriptions is to use row-level security so that people can only see their own data and just subscribe everyone with the built-in subscription mechanism. The tutorial here is just to embed text data in the email; something that the built-in subscription doesn't do.
Thank you very much for the tutorial, just what I needed! :)
Glad it helped!
Wow! Amazing really! Didnt know about this method!! So I can imagine and compose it to my purpose too, like output in Excel, and link to the Report.
Yeah, CSV is a lot easier though - it's usually "close enough" since it opens in Excel. If you want to do .xlsx it'd probably involve creating the file and then adding the rows in a loop.
@@bi-ome Question, I managed to get extract for myself (Email and .csv), for my understanding, how to get different data from the visual? I mean, I changed in Service the filters and when I try to trigger Power Automate, it still gets me old data (my test data). Hows actually functioning with DAX Query? do I need to refresh the desktop version (and put a different filter) and then publish it every time, or it is enough from Service to choose what I need? Somehow Power Automate doesnt get this changes...
p.s Do I need this table that is for export to have it in my report (with Power Automate visual) or?
Hi, when i try to send email, it send emails to all the email address that i have used as per the steps. Generally it should go to 1 person at times if an item has been added or modified in SharePoint
This flow is about using Power BI with Power Automate to send emails, if your source is a SharePoint list you probably want to just use the SharePoint triggers instead and not go through the steps in this video. But there is a for-each loop here that is looping over the recipients and sending an email per, so if yours isn't looping properly perhaps there is an issue with either the loop or the thing you're looping over? If your recipients are in a string instead of an array that could cause this behavior.
@@bi-ome so i have Power BI and a SharePoint point, i am using a Power App (canvas) to create an item in SharePoint. So whenever there is an update in SharePoint list i need an update, i am using a trigger " The flow is as below.
When an item is created (SP)>Run a query against dataset(PBI)>ParseJSON>InitializeVariable>AppendArrayVariable>Compose>SendEmail
So whenever the flow runs it send email to all the entries instead of the new/modified entry.
It send 100 of emails to everyone whose email id is there in the list, i want the flow to send email to just the new entry.
This is a great video but i am getting stuck on the parse json. Power automate gives me a huge output and when i just use the few rows as example for schema i am getting an error due to some fields are empty or null. Checked the input and on some json rows its missing the field. Am i doing something wrong?
The output from sample tries to "guess" information about your data based on the data it has to work with. For the required fields, there's a section where it sets those (it's at the very bottom off the top of my head?). To adjust it, you just remove the fields you don't want to be required from the list of required fields in that JSON section.
Great video! I'm getting an error on the Parse JSON part "Invalid Type. Expected Object but got Array". In the first test, my output is a file to download. Any thoughts on how to solve this?
Yeah, I think I recall actually getting the same error while putting this video together and having to do a re-take when I didn't correctly copy the "example" response from the test run - did you copy the entire output for the creating schema "from example"? You need the entire response, not just the inner nested part if I recall.
@jets3370- did you figure out the solution to this? I am having the same issue. My output is also a file to download instead of the "First table rows" output per the video. And when I copy that output, I get the same error when I copy this result to the Parse JSON. I thought at first it's because I have to many columns but when I reduced the columns it's giving me the same result.
@@CindySalvesen - No, I haven't figured it out yet. I'll have more time tomorrow to work on it. If something changes, I'll let you know
@@CindySalvesen Figured something out...after the 'query dataset in PowerBI' , you can select the Apply for each step, First Rows as Table and add an Action 'Parse from JSON' where you select Current Item and copy and paste the downloaded file. From there pickup with the video.
Disclaimer, I'm very new to this....I'm also getting 1,200 emails when I set this because it's looping around. I need to fix this.
This is a great tutorial on how to dynamically email employees their personal records. In my case, I have two employees associated with the same record (meaning I have 2 email columns in the dataset). How can I dynamically send a single email that is received by both employees with their shared records? I'm sure that's a loaded question lol but a nudge in the right direction would be extremely helpful. Thanks Christine!
Thanks! I would just add the second column to the table, update the query in the flow, and drop the second email address column in the CC on the email (or semicolon after the recipient). As long as there’s always someone in the first field, that should work.
@@bi-ome Thanks for the response! I think I understand what you are saying, but my problem is that the second email will not always be paired with the first email. For example, user 1 could be paired with user 2 on two orders, so one email total would be sent to both of them. Then user 1 could also be paired with user 3 on an order, so one email total would be sent to both of them. In total, only two emails should be sent.
@@TJKubiesa If you are trying to send them a record set, and the second email address field is not the same every time for a single email recipient 1, you would need to just send that second person a separate email with "their" records assuming don't want them to get some stuff that's not their own (just duplicate the table/copy query - can do a new flow or put it in the same flow).
Christine - thank you for making this; it's super helpful! However, on the first 'apply to each' where the emails are being appended to the array variable, I get the error: "The value cannot be null. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'." Is there a way to have power automate ignore the nulls in the data?
I'd just filter your initial DAX query to not include null emails. Ideally where you pull your query from in the Performance analyzer should be precisely the data you're wanting to send out, you can put all your filters on there. Or you could filter on the Power Automate side with filter array, but it's better to just not query what you don't need. BTW there is an update to this video here - it uses a different technique that I prefer, you might check this method out: th-cam.com/video/8YnBotHynD0/w-d-xo.htmlsi=X-In0Q1ctihNEnId
For me, current item is not coming in second apply to each. any idea . why?
Try switching to the classic Power Automate UI - the menu option is in the ellipsis menu next to the "test" button in the toolbar. I've been getting a lot of reports of dynamic content options not showing up in the new UI that just rolled out.
I switched to Classic Designer mode but still didn't see the Current item. Is there any other way to fix this in latest version
@rohitbonde2010 have you managed to fix this issue
You can enter the hard-coded expression for the current item - for me this is "items('Apply_to_each_2')" - it needs to be set as an expression if you do it this way,
@@faizanzamir961 You can also reference the current item by it's "code" as an expression - this will depend on what your step names are, for me it is items('Apply_to_each_2')
I tried your method but I keep getting error message The value cannot be null. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'. and I know I do not have any null values
Check what it's feeding the step in the inputs in the flow history - that will help you see what's going on
Hi , is there a limit for no of rows when we use run a querry against a dataset ?
The documentation says maximum of 100,000 rows or 1,000,000 values per query (whichever is hit first). The latter is a combination of rows and columns, so if you have a lot of columns you'll hit it sooner. If you have more than that, I have another technique that queries and gets one row per recipient instead (it creates the table as HTML on the Power BI side before sending to PA), which usually shrinks it substantially--
@@bi-ome i actually trying to load data from Power Bi to sharepoint list , but same method , runa querry against a dataset and then parse Json , Yes , as you say many columns, lets say 100 . I m gettting only 500 rows
@@bi-ome Can you please share the other technique?
Could someone please help me. Everything works fine besides the fact only headers are being send by email, no actual data. What can I do about it?
Try to figure out where the data is dropping. Check your query response body first and make sure the data is in there; then check the filter step to make sure there is still data in it after the filter. You might have a bad filter that’s returning no results.
I'm getting stuck around 13:07 where I build my HTML table with custom columns. I only see my column names from Parse JSON, *not* Filter Array. Any idea how to fix?
Make sure they're in the same for-each loop? If one is outside the loop, it won't be able to see them.
@@bi-omeok got it. The problem was my sample data set was too large, which resulted in a file download in the Run a querry against a dataset. Once I got that down to show just a few rows, copy/pasting that text into the Parse JSON Generate from sample resolved my issues.
I used this to create a flow that would email each manager a list of their employees with their employee's open tasks. I've been asked if I could do this same thing but send a separate email to the manager for each of their EMPLOYEES. (e.g. each manager might receive multiple emails). How would i modify this to accomplish that?
@@daronodonald9662 If I'm reading your question right, you should be able to use the exact structure that's in the demo, but instead of emailing the assignee, use the "get manager" action before the email to get the assignee's manager and have the email go to them instead. That will send the manager of each employee a list of that employee's outstanding tasks, one email per employee (because the for-each loop is looping over assignees).
Hi Christine,
Rather than using a visual level filter if we use a slicer will it work the same?
Yep! Anything that affects what the visual outputs will work.
Hi Christine. This is super helpful. I read your pinned comment below after seeing that while in my test data I have two rows for the same email address, only one row is being populated in my HTML table. I tried changing "first table rows" to "body," but received an error stating an array was expected, but an object was received. This is so close though! Any idea how I can get this particular Flow past the finish line? In this scenario I'd like to have one e-mail sent with a 2 row HTML table.
** Update - this works for me with "first table rows." I just needed to republish the .pbix with the additional rows I added to the table viz. after I started creating this Flow. Thanks for sharing!
Thank you so much! This is what I was looking for. I only got stuck while adding the owner's name to the email body. Does anyone have any idea? Thank you!
What wasn’t working there? Not sure if you’re getting it from your dataset or from the user profile, but you can use their email address for the “get user profile” action and get their name from there. Otherwise if it’s in the dataset you would have to use an expression to get the first value on the filtered data since it’s got multiple values in it-
Great video, Christine! It helped me a lot in creating something very similar. Do you have any suggestions on how I could also apply a filter or create an array to send one single email by region/location?
Great! It'd hard to say without knowing the shape of your data, but you can absolutely filter the dataset query using "Filter array" (here's the first article I found that explains how to filter - not affiliated with them at all: www.powertechtips.com/filter-array-power-automate/). You can add parallel branches to flows to do multiple things at one time - so for example filter the array by different values per branch, and have a different email for each (you can copy/paste your email action and edit values to make it faster). Parallel branch will pop up as an option when you click a node between actions.
Much appreciated!! Thanks a lot.
Great video , Save me a lot of time...
not wirking with me , Does it require to have licence power automate ?
Yes, but not a premium license - just the standard one that comes in most of the license bundles. Are you getting a specific error?
Hi, this is an amazing video thanks.. i don't know if i am using a different version of power automate😂.. but when you are in the APPEND step i see that all the fields in 'Parse Json' are showed and you select the email field. In my case the section 'Parse Json' only have two options : Body and 'Body Items'.. I don't know how extract only the Email value. I am stuck😂😂
DEFINITELY toggle off the new designer if you're in the new UI. It is missing virtually everything. 😅
Amazing video! Can you provide any guidance how to address mail body using name instead of user id. We need to trigger mail based on unique user id.
Thanks, I'm not sure I follow - you can't address email to a name, but for example if you have a list of which user ID rows need to go to which email address, you could for-each loop over that to reference it in the actions. Unless you mean you want to insert their name in the email body?
@@bi-ome yes we need to address the mail with their name, instead of id. But we are passing variable as userid, so we can get distinct user list.When we insert user name in mail, same user getting multiple mails, instead of unique mail.
@@ramyarangasamy3770 If you need to remove duplicate values, you can do the same Union() step we did on the emails on your user IDs - this gets a distinct list of IDs.
Is it possible to do the same for Paginated Reports in Power BI? We want to embed the table generated from the Paginated Report into the email body just like how it is being done in SSRS. Currently I can't find any other solutions for this using Power Automate, maybe someone can help with a similar approach from this video.
The thing with paginated is it's just a type of report - it uses a semantic model just like a normal report. You can query the dataset that the paginated report is referencing the same way to get a table with data that matches the paginated report, but you can't "get" the paginated table per se. The only paginated-specific action in Power Automate is to send it to a file, not embed it, but you could use that if attaching a file is acceptable.
Hi, thanks for the video.
I am getting this error: Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '8878' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'.
I have added the filter_array in my second Apply to each loop. And in the filter query, I added the array variable initialized.
How can I fix this pleasee?
I’m not sure, it’s hard to say without seeing it. If you want you can send a screenshot to my email in the channel about page and I can try to help (if it has nothing sensitive in it)
Hi,
Thx for this video. It works fine for me, but I'd like to send a CC as well.
I have another column with the agent's email and I want them to receive a CC when their clients receive their related infos.
How can I do that?
The "send an email v2" action has a space to add CC and BCC, you might need to open up advanced settings to see it. You should be able to use any data that's in your initial query, though depending on how your table is structured you might need to append emails to a semi-colon-separated string as a step if there's multiple.
I'm using the CC, but can't find what to put inside to make it work... It has to be part of the same loop, but info is coming from the clients' email.
Sorry, I'm new to Power Update, but can't find any useful hints...
To clarify, the CC will also depend on the clients...
@@andrevachon9685 Yeah, so as long as the data in your table in PBI is structured the way you want, you should be able to use it. You do need to update the "query a dataset" and the "parse JSON" step to include your new table column to get it to show up. For how you shape that data, if you have duplicate values for the CCs you will want to use the same UNION() trick to remove duplicates on those values, OR just structure your original table so that there's one row per email, with a column for the recipient and a column for the CC (would likely require some DAX and some CONCATENATEX()). The thing with all these tools is there's lots of ways to do everything, so it's about picking the way with the least trouble for you.
Thx for not letting me down!
I already did the same thing with the CC, so to have only one. I now have Get distincts emails and Get distincts CC.
When I created the last loop you showed, I need to select the outputs from either one of them, right? So, I don't know what to do to get the outputs from the other one and to make sure the right CC goes according to the right report sent.
I wish I could show you my flow...
This tutorial is amazing, i would be trying this tomorrow and let you know
Hi, does someone know how I can embed a matrix or visual from Power BI in an Email with Power Automate?
You can’t currently embed the literal PBI visual in an email. You can attach a PDF of the report with paginated, but most of the paginated features are premium.
That’s why we query it and put it in an HTML table here-
Great tutorial!
How can I format the table to show money and date?
The date is with hh:mm:ss and currency is with no $ mask.
I tried to manipulate the variables with FormatDateTime(), but it is not possible to select/reference the fields inside the filterarray body().
Yes, you can use the DAX function FORMAT() inside the PBIX (not in the flow) to choose the formatting of the measure. This does go all the way through to the flow end of things, though I would do this in a new measure (vs one that is used in chart visuals) as I think the output of it is technically text.
Thank you! This is really what I am looking for. But one question: if I only send each email with that email-related sell, how do I do it?
I don’t understand what you’re asking, could you rephrase? The flow example is sending the applicable rows to the owner of the rows.
Can anyone tell me how to do this with automate? Thanks! I have a power bi table with 7 columns and hundreds of rows: ‘code’ column, ‘description’ column, ‘quantity’ column, ‘total value’ column, ‘owners’ column, ‘manager’ column, and ‘functional managers’ column. I need to send the table by email to each owner, manager, and functional manager, but only with the rows relevant to them. The rows for owners, manager, and functional manager are populated with their email addresses. Please note that there can be more than one owner (multiple email addresses in the same row).
You can do one query for each audience - eg one for owners, one for managers, etc. How you query it depends on how the emails are structured, if there's multiple in a single field right now you need to split them to rows on the delimiter first in order to get distinct emails. Then you can basically follow the same pattern as the video. Alternately, if you have premium you can use RLS roles and paginated reports to send based on that.
Thank you for your interesting tutorial.
Is it possible to put the username of the user in Windows in a variable?
Like the local user account on the machine? No, Power Automate Cloud has no info about the local machine, since it's the cloud tool. If you have data about which M365 users have which local usernames, or if they follow a precise format, you could either do a lookup on that or generate it though (e.g. orgname\first.last from the user profile).
This was great but I ended up sending myself "600" emails. I only have 3 "projects" and thought I amended the table to the project name and would only get 3 emails. Does anyone know which step may have an issue?
LOL, been there myself😅. It sounds like your filter array isn't filtering, or you're not looping on the filtered values. I am editing another video right now that handles the data structure differently, you might have better luck with that method - should be ready early next week.
For clarity - I did a test flow and am receiving these emails. Does anyone know how to make it stop? 😳
Thank you!!! @@bi-ome
I do have one other question if you have time and don't mind me asking. The Power BI report has a filter that looks for the latest report issued (from another source), I believe (but not 100%) this filter is getting pasted into the query and is possibly preventing my data from updating on my power automate email.
Text from the query (filter part only) below:
FILTER(
KEEPFILTERS(VALUES('DailyLogManpower'[created_at])),
AND(
'DailyLogManpower'[created_at] >= (DATE(2024, 2, 4) + TIME(18, 29, 2)),
'DailyLogManpower'[created_at]
Hello - thank you for the video it was helpful but I've run into a problem. I'm receiving the following error during the test phase: [{"code":"DetailsMessage","detail":{"type":1,"value":"The dataset '226s3700e2-2891' is a model hosted in Azure Analysis Services or SQL Server Analysis Services using On-Premise Gateway, which is not allowed."}} Any suggestions? Thank you
So I did an internet search on your error and this is the FIRST time in my life I have googled something and come up with only one single result - normally it's a million or nothing, lol!
Basically, this action is not supported with the source you're using. You might look into what SSAS/AAS has available for built-in emailing of data, I believe it has something for that (e.g. see: learn.microsoft.com/en-us/sql/reporting-services/subscriptions/e-mail-delivery-in-reporting-services?view=sql-server-ver16 ). Or paginated reports.
@@bi-ome thank you!
how to use multiple filters in power automate and in my case there is a 10 fields to be used for creating HTML table is it possible to add those 10 fields not able to add those its limited to only 4 when we give show raw outputs. Please provide me solution for this?
There are limitations around the total amount of data returned by querying the dataset. There's some methods in this thread that might help: powerusers.microsoft.com/t5/Using-Connectors/Run-a-query-against-a-dataset-doesn-t-show-all-data/td-p/1700884
@@bi-ome We have 25 columns in the dataset to run . the input we are passing in run query in dataset is 25 columns but when we run in output we are getting only 4 columns.Can you please suggest possible ways to get all 25 columns in the mail.
@@sriramram9594 I do not think this technique is meant to be able to handle 25 columns. You might consider the paginated report subscriptions instead?
Thanks, you're a life saver :)
Hi great tutorial. I have just one problem. I have employees attendance data in power bi and everytime I refresh it in power bi with relevant date, dax query and parse jason should also get updated automatically. But its not the case. How can I resolve this? Please guide
Are you refreshing in the desktop app or web app? The refresh needs to be done on the web version in order for the query to read the updated data.
Hi Christine great video, can you do a power automate video that Exports Power bi Reports to CSV with the recurrence schedule being the trigger action. The report has a filter connected to a table where we get the data. This filter changes from time to time depending on the user's requirements ( start date - end date) end date changes. I want the automation to reflect these changes on the filter
Do you mean exports a table in the report or the entire report? I have a video here that creates CSVs on a schedule from a JSON response and stores them in SharePoint, if you take the query a dataset action and use that instead of the "get items" in the video, that should do it: th-cam.com/video/RBx-HbVpWTQ/w-d-xo.html.
To get the start/end dates, you could add two queries to the flow get the min/max dates from your dataset (unsure what the requirement is here... you could put them in the filename?).
The requirements are as follows; Create an automated solution for exporting a Power BI report table to CSV format and then sending this CSV output via email to a designated recipient. Notably, the users who will be receiving the CSV output do not possess direct access to Power BI.
Furthermore, it's essential to consider that the data presented in the Power BI report table is influenced by a slicer filter. Thus, any modifications made to the slicer filter by the user should result in corresponding changes within the automated CSV output. For instance, if a user specifies a particular date range in the report (e.g., Start date: 09/02/2020, End date: 08/03/2022), the data visible in the Power BI table should accurately match the data contained within the CSV output generated by the automation process@@bi-ome
@@bi-ome So far I have a flow that goes like Reccurence-scheduled (TriggerAction) > Run a Query Against a Dataset(PowerBi) > Create CSV table > Create Onedrive File(optional) > Send an Email v2(Outlook)
Under the (Run a Query Against a Dataset) the DAX query I got from the performance analyzer is static, hence it does not reflect any changes made on the report after it's entered on power automate, so the CSV output won't match the report if a change is initiated from the filters
@@BAROS_Z Oh, yeah, I think the only way to get the user's applied changes is to use the Power Automate button visual or the Power Apps visual. The "query a dataset" is not going to be aware of what the user is doing in the report. I thought you meant that the source data was changing, which it would work for.
The built-in subscription service lets the user sign up on a schedule that takes their applied changes - have you tried that? learn.microsoft.com/en-us/power-platform-release-plan/2022wave2/power-bi/subscribe-reports-filters-slicers-applied
@@bi-ome The data on power bi is connected to a live source, hence the use of the slicer filter. In this case The Power Automate Button works perfectly however the stakeholder are insists on not having a button. ( can you set restriction on who has access to the button?)
Yes, I have tried the subscription service however some recipients to the report done have access to Power Bi.
My mind is going in circles trying to accomplish this task.
Is there a way to get matrix tables?
Not that I know of, if you really want the pivoted matrix your best bet is the “analyze with Excel” that links to the model, or just the normal report subscriptions-
@bi-ome thanks! I'll search about it
Thank you so much❤
Hey, Christine
Let's say I want to send the sales report to their owners only if each of its product price increases by 100. Can you please make video on this scenario?
All you’d need to do here is set up the visual you’re getting the DAX query from to reflect what you want. So in your table, put your account owner emails, product price change amount in whatever timeframe you’re after (as a CALCULATE() measure), and add a visual-level filter to the table for that measure > 100. Then copy that query from the optimizer and use it in the flow.
Also whenever i copy the dax query to power automate it only has hard coded date ,even if i filter it by past week. Any idea on how to resolve that? @@bi-ome
I am getting an error after i copy the query from the perfromance analyzer it {"error":{"code":"PowerBIFeatureDisabled","pbi.error":{"code":"PowerBIFeatureDisabled","parameters":{},"details":[{"code":"DetailsMessage","detail":{"type":1,"value":"'DatasetExecuteQueries' is not enabled for the tenant."}}],"exceptionCulprit":1}}}, how can this be fixed
Sounds like the feature is disabled and you need to talk to your administrator - you can probably ask IT who that is :)
@@bi-ome thank you very much
Hi Christine, great video
How can i use DAX function FORMAT() inside PBIX?
The documentation is here: learn.microsoft.com/en-us/dax/format-function-dax
If you have trouble, ChatGPT is quite good at writing you a statement for formatting if you give it an example and your column names.
im getting "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '4127' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'." error
How do i resolve it?
I would take a look at your filter array step and make sure you have the correct thing inserted there. Here is an article on the error you're getting: powerusers.microsoft.com/t5/Building-Flows/Action-Apply-to-each-must-be-a-parent-foreach-scope-of-action/td-p/1266735
@@bi-ome Issue Resolved, Thanks🙃
"Awesome tutorial! This is what I was looking for. Quick question: How can I include a card visual indicating Total Count in the email, being filtered by the email of the recipient?
Thank you!
I would guess that you could do it in the exact same way. Getting the query should work the same for any visual type, I just happened to use a table. You could embed it in the email in a single-column table. If it needs to be fancy you’d need to do custom HTML.
@@bi-ome Thank you!
Amazing video! Can you provide any guidance for putting multiple visualizations directly into an email, filtering the results for each recipient?
You can repeat the actions on a different “query a dataset” to get another table below the steps for the first but above the email. You have to decide which is going to drive the “to” addressees for the email, but it’ll let you insert multiple tables in the body.
@@bi-ome Okay, Thanks! I know you mentioned it at the end of the video, but if you can provide any guidance at all as to how I can customize the table apperance in the email, that'd be wonderful! Any direction would be greatly appreciated.
how to schedule this email?
When you create the flow, one of the options from the create a new flow menu is a scheduled flow. You can choose the start date and schedule there-
Thank You Mam, GBU.
Thanks!
Saviour!
👍🏾noice
could clearly see its you - but was so damn ROBOTIC. I couldn't get along. Pls try putting more tone and expression if possible.
Thanks for being my first official negative comment! Jokes aside, I do need constructive criticism. So thanks :)
There are other better ways to do this
What’s the better way to embed data in the email? I’m curious
Thanks!