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! kzitem.info/news/bejne/mY-kqKOqgaufd2U
@isBessem
7 ай бұрын
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?
@amazingbandproyects3253
4 күн бұрын
Hello Christine! Thanks, it worked perfectly for me. I can now notify users individually! Thank you very much! : )
@bi-ome
7 ай бұрын
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/
@kirans6760
7 ай бұрын
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.
@bi-ome
7 ай бұрын
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!)
@cocoville9289
Ай бұрын
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
@bi-ome
Ай бұрын
Totally! good tip 🙃
@bi-ome
Жыл бұрын
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!
@GIbrAvila
Жыл бұрын
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
@oluwatosinkoyejo6067
7 ай бұрын
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.'.'.
@maheshgupte1495
8 ай бұрын
Dear Christine, Thank you very much for making this video.
@himmuzz4378
2 күн бұрын
Thank you so much! You are the GOAT
@AmkorTechnology
Жыл бұрын
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!
@bi-ome
Жыл бұрын
Sure! Glad it was helpful!!
@bonsai_mad8139
Жыл бұрын
Exactly what I needed - been searching on how to do this for ages. Great vid!!!
@shilpigupta8392
Жыл бұрын
Your detailed video helped me today achieve what I was looking for! Many thanks !
@TJKubiesa
2 ай бұрын
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!
@bi-ome
2 ай бұрын
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.
@TJKubiesa
2 ай бұрын
@@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.
@bi-ome
2 ай бұрын
@@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).
@personagem97
5 ай бұрын
You're the real MVP Christine, thanks for the video. Helped me alot
@nathanandmycountries
8 ай бұрын
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
@bi-ome
8 ай бұрын
Good catch!
@regashmohammed1989
3 ай бұрын
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?
@bi-ome
3 ай бұрын
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.
@21randyo
3 ай бұрын
Thanks for your time/help! Also, saw your reddit acct in your Gantt video and realized you've helped me on there too 😭
@bi-ome
3 ай бұрын
Aw thanks haha!
@user-kl8ye7dh9c
3 ай бұрын
Thank you so much !!! don't stop uploading content 👏💪
@ryanhamilton3609
11 ай бұрын
This is going to be very useful to me. Thank you very much for making this video 😍
@bi-ome
11 ай бұрын
So glad it was helpful!
@user-bl2zc7fn8l
Жыл бұрын
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!
@bi-ome
Жыл бұрын
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
@darrenmann8095
8 ай бұрын
This was exactly what I as looking for. Thanks!
@mirrrvelll5164
4 ай бұрын
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.
@bi-ome
4 ай бұрын
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.
@mirrrvelll5164
4 ай бұрын
@@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?
@mayahananaev9350
11 ай бұрын
Hi, Thanks for the video. How can I format a number to currency? Thanks
@bi-ome
11 ай бұрын
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
@mayahananaev9350
11 ай бұрын
Thank you. worked :)@@bi-ome
@eladiohernandez6482
7 ай бұрын
Love it! Great video.
@MehulPatel-hg4hc
10 ай бұрын
Hi Christine. Thanks for the tutorial. Is there a way to sort one of the columns from the PowerBI Dataset in the HTML table?
@bi-ome
10 ай бұрын
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.
@burton11ato
9 ай бұрын
Top tier walkthrough and narration. Thanks!
@lalitabehera4158
3 ай бұрын
i have to keep date selection dynamic filter., any suggestion on how to keep dynamic DAX query
@bc67
4 ай бұрын
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!
@mentorcardinals9
4 ай бұрын
You are amazing! Thanks for this. Helped me out tremendously. Subscribed!
@albnic87
10 ай бұрын
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?
@bi-ome
10 ай бұрын
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.
@albnic87
10 ай бұрын
Much appreciated!! Thanks a lot.
@CeliaRostow
2 ай бұрын
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?
@bi-ome
2 ай бұрын
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
@CeliaRostow
2 ай бұрын
@@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!
@ZaheerAli-cj5cd
3 ай бұрын
Nice video. It helped me step by step to achieve
@saramarano1357
Жыл бұрын
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.
@bi-ome
Жыл бұрын
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"...
@saramarano1357
Жыл бұрын
Great, thanks a lot Christine
@samueldemise9895
Жыл бұрын
@@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?
@bi-ome
Жыл бұрын
@@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.
@prateekjain1974
Жыл бұрын
This tutorial is amazing, i would be trying this tomorrow and let you know
@Evaldas65
10 ай бұрын
Thank you very much for the tutorial, just what I needed! :)
@bi-ome
10 ай бұрын
Glad it helped!
@DavidNacimientoLengua
11 ай бұрын
thank you very much don't stop uploading content
@Rishiiii19
Ай бұрын
Thank You Mam, GBU.
@qaisarali646
7 ай бұрын
thanks its useful, only concern is if you have more data query fails , any other option you may know to tackle that issue
@bi-ome
7 ай бұрын
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.
@explorewithnita
Жыл бұрын
Thank you so much❤
@nadian4341
Жыл бұрын
Hi Christine, Great video :)
@bi-ome
Жыл бұрын
Thank you!!
@manouchehr777
5 ай бұрын
Thank you for your interesting tutorial. Is it possible to put the username of the user in Windows in a variable?
@bi-ome
5 ай бұрын
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).
@shivamsarin1
4 ай бұрын
Thanks, you're a life saver :)
@MattAshleyFranck
3 ай бұрын
Thanks!
@feng3625
Жыл бұрын
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?
@bi-ome
Жыл бұрын
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.
@camashouse
6 ай бұрын
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?
@bi-ome
6 ай бұрын
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: kzitem.info/news/bejne/mY-kqKOqgaufd2Usi=X-In0Q1ctihNEnId
@carlosgomez-df9cg
7 ай бұрын
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😂😂
@bi-ome
7 ай бұрын
DEFINITELY toggle off the new designer if you're in the new UI. It is missing virtually everything. 😅
@philp2109
29 күн бұрын
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.
@bi-ome
29 күн бұрын
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.
@marlincantos548
5 ай бұрын
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!
@bi-ome
5 ай бұрын
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-
@raghav_rao_30
10 ай бұрын
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??
@bi-ome
10 ай бұрын
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.
@hotdock2
8 ай бұрын
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?
@bi-ome
8 ай бұрын
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.
@chandreshmehta2651
4 ай бұрын
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
@bi-ome
4 ай бұрын
Check what it's feeding the step in the inputs in the flow history - that will help you see what's going on
@Moleo26
11 ай бұрын
Great video , Save me a lot of time...
@21randyo
3 ай бұрын
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?
@bi-ome
3 ай бұрын
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.
@21randyo
3 ай бұрын
@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.
@bi-ome
3 ай бұрын
@@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.
@21randyo
3 ай бұрын
@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.
@bi-ome
3 ай бұрын
@@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!
@jets3370
Жыл бұрын
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?
@bi-ome
Жыл бұрын
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.
@user-bl2zc7fn8l
Жыл бұрын
@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.
@jets3370
Жыл бұрын
@@user-bl2zc7fn8l - 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
@jets3370
Жыл бұрын
@@user-bl2zc7fn8l 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.
@arunv1909
4 ай бұрын
Hi , is there a limit for no of rows when we use run a querry against a dataset ?
@bi-ome
4 ай бұрын
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--
@arunv1909
4 ай бұрын
@@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
@arunv1909
4 ай бұрын
@@bi-ome Can you please share the other technique?
@ritamgiri5872
6 ай бұрын
Hi Christine, Rather than using a visual level filter if we use a slicer will it work the same?
@bi-ome
6 ай бұрын
Yep! Anything that affects what the visual outputs will work.
@MettetenDolle
3 ай бұрын
Hi, does someone know how I can embed a matrix or visual from Power BI in an Email with Power Automate?
@bi-ome
3 ай бұрын
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-
@ylemu474
Жыл бұрын
"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!
@bi-ome
Жыл бұрын
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.
@ylemu474
Жыл бұрын
@@bi-ome Thank you!
@hemanthkumar3863
Жыл бұрын
Hi Christine, great video How can i use DAX function FORMAT() inside PBIX?
@bi-ome
Жыл бұрын
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.
@elvir24
10 ай бұрын
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.
@bi-ome
10 ай бұрын
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-
@elvir24
10 ай бұрын
@@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.
@bi-ome
10 ай бұрын
@@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": kzitem.info/news/bejne/rY-rmaunm2R_q2ksi=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. :)
@elvir24
10 ай бұрын
@@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. :(
@bi-ome
10 ай бұрын
@@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): kzitem.info/news/bejne/kqul35WsmKF6ZIosi=9N5DQzPkTBqKxiY8
@luisfernandoidalgo5774
Жыл бұрын
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().
@bi-ome
Жыл бұрын
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.
@muhammadfurqan3351
Жыл бұрын
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
@bi-ome
Жыл бұрын
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.
@rickj4196
5 ай бұрын
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
@bi-ome
5 ай бұрын
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.
@rickj4196
5 ай бұрын
@@bi-ome thank you!
@ramyarangasamy3770
9 ай бұрын
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.
@bi-ome
9 ай бұрын
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?
@ramyarangasamy3770
8 ай бұрын
@@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.
@bi-ome
8 ай бұрын
@@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.
@vu111
10 ай бұрын
not wirking with me , Does it require to have licence power automate ?
@bi-ome
10 ай бұрын
Yes, but not a premium license - just the standard one that comes in most of the license bundles. Are you getting a specific error?
@ltbabyj
7 ай бұрын
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?
@bi-ome
7 ай бұрын
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.
@ltbabyj
7 ай бұрын
For clarity - I did a test flow and am receiving these emails. Does anyone know how to make it stop? 😳
@ltbabyj
7 ай бұрын
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]
@rohitbonde2010
9 ай бұрын
For me, current item is not coming in second apply to each. any idea . why?
@bi-ome
9 ай бұрын
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.
@faizanzamir961
9 ай бұрын
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
@faizanzamir961
9 ай бұрын
@rohitbonde2010 have you managed to fix this issue
@bi-ome
9 ай бұрын
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,
@bi-ome
9 ай бұрын
@@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')
@PLUNSCI
11 ай бұрын
Amazing video! Can you provide any guidance for putting multiple visualizations directly into an email, filtering the results for each recipient?
@bi-ome
11 ай бұрын
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.
@PLUNSCI
11 ай бұрын
@@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.
@daronodonald9662
11 ай бұрын
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-ome
11 ай бұрын
Make sure they're in the same for-each loop? If one is outside the loop, it won't be able to see them.
@daronodonald9662
11 ай бұрын
@@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?
@bi-ome
11 ай бұрын
@@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).
@user-gm2nz8oe7u
9 ай бұрын
how to schedule this email?
@bi-ome
9 ай бұрын
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-
@shivamgovekar1757
Жыл бұрын
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?
@bi-ome
Жыл бұрын
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.
@shivamgovekar1757
Жыл бұрын
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
@sriramram9594
Жыл бұрын
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?
@bi-ome
Жыл бұрын
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
@sriramram9594
Жыл бұрын
@@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.
@bi-ome
Жыл бұрын
@@sriramram9594 I do not think this technique is meant to be able to handle 25 columns. You might consider the paginated report subscriptions instead?
@BAROS_Z
Жыл бұрын
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
@bi-ome
Жыл бұрын
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: kzitem.info/news/bejne/s3iuk3yYj6KIh4Y. 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?).
@BAROS_Z
Жыл бұрын
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
@BAROS_Z
Жыл бұрын
@@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
@bi-ome
Жыл бұрын
@@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
@BAROS_Z
Жыл бұрын
@@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.
@user-os9nj3gs6i
Жыл бұрын
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
@bi-ome
Жыл бұрын
Sounds like the feature is disabled and you need to talk to your administrator - you can probably ask IT who that is :)
@user-os9nj3gs6i
Жыл бұрын
@@bi-ome thank you very much
@innergizewithkritika9324
11 ай бұрын
Saviour!
@andrevachon9685
7 ай бұрын
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?
@bi-ome
7 ай бұрын
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.
@andrevachon9685
7 ай бұрын
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
7 ай бұрын
To clarify, the CC will also depend on the clients...
@bi-ome
7 ай бұрын
@@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.
@andrevachon9685
7 ай бұрын
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...
@shivamgovekar1757
Жыл бұрын
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?
@bi-ome
Жыл бұрын
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
@shivamgovekar1757
Жыл бұрын
@@bi-ome Issue Resolved, Thanks🙃
@cathrerinezetadrones3169
7 ай бұрын
👍🏾noice
@datalearningsihan
Жыл бұрын
There are other better ways to do this
@bi-ome
Жыл бұрын
What’s the better way to embed data in the email? I’m curious
@ShreyasSinha
11 ай бұрын
could clearly see its you - but was so damn ROBOTIC. I couldn't get along. Pls try putting more tone and expression if possible.
@bi-ome
11 ай бұрын
Thanks for being my first official negative comment! Jokes aside, I do need constructive criticism. So thanks :)
Пікірлер: 194