Send STYLED Lists or Tables from Power BI to EMAIL or Chat with Power Automate
Вставка
- Опубліковано 22 лип 2024
- This is a tutorial on how to use the “Run a query against a Power BI dataset” action in Power Automate to send formatted, styled data to users via email or Teams chat message. This can be row data, aggregate data, your measures with or without conditional formatting, whatever you like. You can send the messages to assignees, owners, or any other user attached to your data rows (assuming you have their email addresses).
The gist of it is we concatenate HTML tags containing CSS styles with our data, pass it to Power Automate, and insert it in email/Teams chat. This demo shows a bulleted list and a table, but you could do other crazy things here too.
This video sends data on a schedule, but you might also be interested in the Power Automate visual button that we use to send notifications for selected table rows here:
• Use the Power Automate...
The PBIX used in this video is available here:
github.com/chpayton/BI-Files/...
0:00 Intro/what it looks like
2:15 Create the item hyperlinks (if using)
5:33 Collect what you want to send in a table visual
6:30 Bulleted list DAX
11:45 Create the flow
13:23 Copy the DAX query from the table
15:40 Parse the JSON & loop over results
17:38 Put data in Teams message
19:16 Put data in email
20:55 Styled tables
27:46 Add aggregate measures
29:05 Update the flow - Наука та технологія
Thanks for all your help!!
Thanks a bunch, very helpful, exactly what I was looking for!
Very helpful, appreciated
Congrats on the well-explained video.
Great Project management tool! Good delivery Christine ! However, both video are helpful and one should not be the replacement of the other as this one is more technical than the previous one, please keep the two
Thanks for the feedback, I was considering removing the older one but will keep it around I think 😊
Hi Christine, do you perhaps have a video on embedding Power BI visuals (graphs etc..) as PNG format in the email body using automate?
I think the only way you could get specific visuals would be with Power Automate Desktop, by having it open it in a browser and take a screenshot at specific x/y coordinates lol. Usually the way people get the visuals is via paginated reports and attaching the whole report as a PDF, but that requires premium capacity and I don’t have premium. 😢
Hi Christine, thank you for the great videos! I was wondering how I would filter the output by distinct emails as in the first video when using the Body instead of the First Rows? I either end up with spamming emails for each row or getting errors unfortunately. Any tips would be appriciated :)
The distinct filtering in this method is done on the Power BI side in DAX. So we add our user field to the table visual in PBI and use concatenatex() to create the HTML tables - we are iterating over the distinct assignees with the DAX. Then we just have to loop over the body rows in Power Automate to send the emails because it’s already one row per user when it’s passed to PA.
So the trick here is in getting a good understanding of how concatenatex() and iterator functions work. You can mimic the DAX in the video, but depending on your model you might need to modify it somewhat - it uses your table relationships which are prob much different from mine
@@bi-ome OK, I see. I tried making the HTML-table directly in PBI as in this video, however my dataset is large enough for me to run into performance time-outs when using too complex measures unfortunately. But maybe I should try to adjust that some more to see if I can get something which will actually run and not time out. Thanks again :)
@@sebastianchristophersen190 How big are we talking? It should be able to handle a fair amount, I've used concatenatex() on datasets in the millions of rows before, though it'll take longer the more columns you have in your table visual. Maybe something wonky or non-optimal in the model?
@@bi-ome Yeah, I'm not the actual "owner" of the dataset in question, so I don't have the full scope, but I'm fairly certain we are in the quite a lot of millions of rows accross a multitude of tables which have been connected together, in addition to quite a lot of measures. This also made the measure I tried to make in PBI for the HTML-table probably not the most optimal either, so I'll revisit that again I think to see if I can simplify it somewhat maybe. When it failed I just thought I might be able to do something in the PA part instead which was easier :)
Great video Christine! It gave me a great lead on my issue! Do you have any idea how I change this to send a scheduled email that contains a CSV file with all the data in a table? in Table format inside the CSV. Like, if you have a simple table with headers and you want to send that in a scheduled email. I used the DAX to get the columns I needed but can't populate the CSV in table format (only getting a CSV with all the data in one row...) I'm searching online and can't get an answer for this... TIA!
Thanks! A csv is a file with all the data (comma-separated values) in one row - it’s only when you open it with Excel that it’s a table. But the ‘create CSV’ action will create a CSV and you can use the create file action to create the csv file in SP. Make sure to remember to put .csv at the end of the file name in that step.
If you’re making a CSV, don’t use HTML tables like in this video btw, just use plain data rows. :)
@@bi-ome Yesterday I was finally able to create the CSV with a table inside it! I used a combination of 3 videos with one of them is yours! Thanks so much! I love your on screen notes! Very helpful!
Thanks!
Eeeee thanks!! 😊
following along, but what will happen when the parse json data changes when the data is refreshed? Will this still work and use the current refreshed data? thanks
The Parse JSON is only info about your data, not the data itself. When you paste the output in, you’re just giving it an example to use, so as long as your data structure and field names don’t change it will be fine. The “from example” won’t always get things 100% either, so it helps to scroll through what it uses for the schema and make sure it looks ok - e.g. if you could have blanks in a field, make sure it hasn’t labeled it as required, that sort of thing.
If you make schema changes on the dataset source, you’d need to update the schema on the flow side to reflect what changes, but it’ll use whatever data the query feeds it when it runs.
Would you know how to do the same process but make it a matrix instead?
I would be looking to send out a matrix per person, with date as the rows, projects as the columns, and hours as the value in the table.
This table above works well, but end up with a lot of rows when sending a row per project per week.
Thanks!
I'm not sure how you would do that unpivoting with the HTML columns. I would just put the projects in the HTML table rows, add your date field as a column, and use a measure that takes the sum of hours in a second column - that should give you a sum of hours by date and project for each person.
Hey Christine! Great tutorial ! QQ , if I need to create a flow for 33 different individuals basically 33 different tables can I do that and create the flow to go to different emails? What I was thinking to do is every morning to send them a table with their orientations / interviews that they have for the day but it has to be with different different tables . My apologies if you already explained that on the video , the plan task got me to confirm with you if that’s possible
Thx! You shouldn’t need 33 tables, the idea is you drop your email user column in and concatenate the rest and end up with one row in the table per email. So all the table rows go into a single cell in the table visual, but come out the other end as a full table for each person in the email due to the HTML.
Hi Christine, cant we copy the output of power bi query directly to the schema?
For the Parse JSON? You can, if I recall I think I did it another way in the video so that you don't have to add a bunch of for-each loops to get to the rows. It's nested a few levels down
Thanks Christine, your channel is really helpful! I tried to do a similar flow but my email output keeps returning the same values, the JSON is not refreshing and is repeating the older data. What could possibly be the problem?
Does your semantic model have refresh scheduled? It’ll only return different data when the Power BI refresh runs. You can do a manual refresh and rerun to check-
@@bi-ome Yes, the semantic model is set to refresh every 2 hours.
@@user-ud1vg5vm6p Is it updating the data in the report? If it’s updating there and your query is correct, it should pull whatever is in the model unless you have extra stray filters.
@@bi-ome well, I do have a lot of filters on the table, maybe I can try removing them and add conditions to the flow instead… 🤔
@@user-ud1vg5vm6p You should be able to see a preview of what data is being queried if you put it in a table visual in the report like we did in the video. Since we copied the DAX query directly from the visual, it should always reflect what the visual is showing assuming it's published and refreshed and you're comparing it to the published version of the table.
It took some ChatGPT help, but I successfully created a flow using your steps that sends a notification with 2 HTML tables of information taken from 2 tables in PowerBI. Based on my test data, I have a visualization with 4 rows: 4 users with 2 HTML tables for each user. Works great right after I publish PBI and then test my flow. PBI Online shows the 4 users also.
I will tinker in PowerAutomate for a bit and re-test and suddenly my flow is sending to ALL users from both queries, which is closer to 50, instead of the 4 in the visualization. I just checked PBI online and it is indeed all users and not the 4. Any idea what is happening after running my flows? Why the visualizations are getting messed up after running the PowerAutomate flow??
If you update the visualization, like filters, make sure to re-copy the query from the visual and put it into the query a dataset action in PA. To troubleshoot, you can set the recipient on the email to yourself so you get all the emails temporarily. Check the DAX you’re using to make sure it has the filters you want there too - you can modify it if you need to.
The flow won’t affect what shows in the PBI report at all, but the web version of PBI does like to save applied filters for the current user. There’s a reset arrow in the top right that will reset to the report defaults. Power Automate is going to use whatever filters were applied in the desktop when you copied the DAX query, by virtue of those coming through in the DAX itself, it doesn’t use the visual web report at all - just the semantic model.
Hello! What process can I use to send 2 different Excel files through a single email?
You mean CSV files? I would look for a different tutorial on creating CSVs, this technique is for embedding HTML tables. You can add CSVs as attachments in the advanced section of the "send an email" action, but this tutorial doesn't create a CSV so don't try to attach the HTML table :)
@@bi-ome Then let me rephrase my question: How can we send 2 HTML tables in the same email?
I was able to replicate this process so this tutorial definitely works.
I'm pretty new to powerautomate and was wondering how I would alter this process if I wanted to populate a single email with two different tables? Both tables include the same field being used to send the distinct emails.
Personally I would just tack on another column to the same table we used in this tutorial for your second table, and use the same technique. That way when you query your dataset, you have both tables available to you as dynamic content cards in the same output - you can drop them both into the email in the loop.
When you update your visual, make sure to re-copy the DAX query and paste it into the "query a dataset" so that it gets both of the table columns. :)
@@bi-ome That makes sense. Thanks for your help.
Hey Christine...
In my Power BI DataSource I have a unique KEY Column (ReferenceID). How can I get the flow to only add records the SharePoint list where the REFERENCEID is not already there?
If I was to change the RefereneID field in the SharePoint list to "Enforce Unique Values" = Yes, would that force the Flow to skip the duplicate value and continue on, or would the flow fail and exit out? Thanks....Rob
I'm confused, is this related to the video topic or no? It's okay either way, I just don't get the context lol. You are creating SP items from a Power BI dataset? There's a few ways to do this:
1. Pull some basic info from the SP list into your PBI dataset, and make a conditional column there that tells you if it exists in SP based on a join between the two. You can use that in your DAX query in Power Automate to only pull things that don't exist in SP to start with, so that you're only running it on what you need in the first place
2. If that isn't preferred, you could do a "get item" step in the row loop that tries to get the SP item. Then add a parallel branch below that, and on one branch create the SP item and set the run after to "success" of the get item step before it, and on the other branch just continue the flow. That way it will only create the item if it can't find it in the "get". Set whatever comes after the parallel branch to run after success or failure so that it keeps going (assuming that's appropriate, there does need to be something after that runs on failure for it to keep looping)
Thanks for you help
👍🏾
Sorry I got lost after you started with the emails, and not sure what to do if I just want a simple table to be sent to specific emails. I think this was a bit over complicated to follow, it was not very clear for me. This is constructive criticism, don't take it bad.
Yeah, that's because it's a fairly complicated thing to do - there's not what I would consider an "easy" way to pull data from Power BI into an email in Power Automate. If you want a different technique, this one does most of the work on the Power Automate side instead of Power BI (but is not styled): ua-cam.com/video/5-0KH0IiDjU/v-deo.html
This is next level, super impressive!
This step "apply to each" nested, I am getting additional step "For each"...and doesn't look as you have or you didn't show it?
There’s at least a couple loops nested in here, yeah. Not sure which is giving you trouble, but make sure you have the modern UI turned off because it tends to add extra loops-
@@bi-ome Ohh you are damn right! I struggled a lot using this "new experience", but it works now better =) thanks for that!
One ad hoc question, is there a possibility to add some kind of "RLS in Chat" so that User sees only stuff for his/her position? I managed to combine some stuff from your previous video (on this topic) and this one, but somehow I didn't find a plain and bold solution to this. I managed to send these chats, also included Link to the report (and there RLS works!!), but not in chat.
@@mirrrvelll5164 In this case we structured the items in the Power BI for that - so in the table visual that we pull the query from, we put whatever the user's name field is in as what amounts to the "row context" and then use concatenatex() to concatenate the values as table rows for each person. Concatenatex() is an iterator, so we have it iterate over users and create a table for each one inside the table column. That does the user-level filtering for us. When you added the user column to the table, it will naturally keep distinct values, so you end up with one row per user, with one subtable per user - then in Power Automate, you loop over the user rows and shove the associated table into email or chat. So we don't actually need to filter the table on the Power Automate side, because the DAX already did that for us on the Power BI side. Does that make sense? You leverage Power BI more heavily to do the transforms/filtering in this case--
@@bi-ome Ah okay okay, yes it makes more sense. I thought I can do prolly the hard way on PA side, but you are right. I will try to implement it then. Thanks for your support!
@@mirrrvelll5164 Sure! There's multiple ways to do everything, but I feel like this way lets you do less lift with Power Automate which is usually the bottleneck resource-wise.
Thanks!
Sure! Thank you for being my first ever super-thanks ;D