Advanced HTML Tables in Power Automate - Formatting cells, rows and vertical tables
HTML-код
- Опубликовано: 28 июл 2024
- In this video I'll be showing you how to build advanced HTML tables in Power Automate. We'll explore how to highlight specific rows based on a condition, change the color of cells, and even flip data into a vertical table.
Out of the box, Power Automate has an action that creates an HTML table from a data source like a SharePoint list. But what if you want to take it a step further? In this tutorial, I'll guide you through some advanced techniques to make your HTML tables stand out.
Before we dive in, we'll go over the basic principles of HTML. Then, I'll demonstrate how to highlight rows based on a condition, change cell colors, and flip data into a vertical table.
If you're interested in learning these advanced techniques, make sure to give this video a like and subscribe to my channel. Let's get started with the demonstration!
00:00 Intro
01:47 Plain old create an html table
03:29 HTML Table understanding
07:08 Create HTML table ROWS via Select
11:05 Create Table Via Compose
13:14 Method 1: inline background and text colour
17:55 Method 2: using css tags for red text
19:25 Format Numbers to include decimal places and £
21:18 Create a Link via a href
23:10 Vertical HTML Table
www.w3schools.com/html/tryit....
Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ 
Наука
Thanks Damien. Today you saved my life with two different videos. I really appreciate it.
No problem Fabio, pleased to have helped.
Great vid as usual Damien! One of my struggles is making power automate "replicate" what PowerFX can seem to do to a table very quickly. And while PowerFX can indeed handle these type of things quickly, the real struggle is when you have a big amount of data and you now need Power Automate to do the "heavy lifting". Your vid is great help! Thank you!
An understanding of select is definitely advantageous for heavy data manipulation. If you’ve got any interesting use cases, drop me a message.
Amazing tutorial, everything explained perfectly. Thanks!
Thank you Lukas.
Thank you sir! My problem was I wanted to get this done quickly so I was skipping away like a ballerina but after 500+ tabs of googling on this topic, I decided I'm gonna just do exactly how you have it from the start and IT WORKED WONDERFULLY! THANK YOU SO MUCH!
😍 thank you Amelia
This really helped, as i have a query on 3 lists (parent, 2 child lists) and wanted to bring the latest details into a table. The select after the apply to each to grab the results from the array variable was perfect! Thanks for the detailed explanation!
Nice one Damien! - Have had my head buried in a Power Apps build for a while and now need to plan the reporting/output aspects. I'm looking for good reporting examples and this video ticks most boxes on my list. Cheers for sharing 👍
Nice one Gerard, good to hear from you and have fun 👍
That is all brilliant stuff.
Thank you so much for this! I had been struggling with the « Create an HTML table » action, especially adding hyperlink as the action changed a bunch of characters that then had to be replaced in order for the href to work. Your way of doing it made it so much easier! The boss is going to love the conditional formatting 😉
Small question, I’m not sure how to add the specific icon I’d like to. I’m using data from planner so being able to add a half-filled circle and an empty circle based on Status would be awesome. Not sure how to implement it here. Would a unichar() value work?
Interesting. Could you use emojis? You could try unichar or SVG? Let me know how you get on.
@@DamoBird365 so I ended up going to a site that converts the unichar to an emoji that can be copied, just like in your video, then it worked perfectly!
Thank you sir, you saved me! 😊
Happy to help 🥳
Thank you so much!!! This video saved my sanity 😁
Hey no problems 👍
Great timing Damien, I have a ta ke to create tomorrow using a lot of those things.
My scenarios is from one list I have an array of email addresses already extracts from one list.
I then have to filter the second list by the email addresses in the first array.
Extract those records
Then put the data into a table.
The other data in the second table has a multiple value choice value column for skills
I need to extract the persons display name in one column.
The skills as comma separated values in the second column.
Going to break it down into chunks see if I can work it out.
Bit of a monster flow doing a mail merge type functionality.
One thing I use a lot of in this one is objects in variables (could be composes) to use if expressions to convert / transform data then use the variable outputs to process the data. Been meaning to blog about it as a pattern but not gotten round to it. Saves a ton of steps and having to go down a flow and changing multiple values as you only have to change the values at the top.
Hi Phil, how’s it going? Sounds like the combination of ruclips.net/video/PD980sKKx0E/видео.html and ruclips.net/video/pwJ73jFEtDQ/видео.html might help if you can face listening to me for more 😂
@@DamoBird365 thanks for that Damien
I don't want to use 'Create an HTML table' anymore! Thanks a lot!
😂👍 sounds like 🫵 are having too much fun 🤩
This indeed saved my life Damien!! Thank you so much! Just one quick question - is there anyway to have multiple lines in one cell for the vertical table? I tried using “replace” with but it always showed error “the first parameter is not string” but null. Wondering if you have any insights on this?
line1line2 should work. What string are you replacing on?
Thanks, great vid Damian! Glad your back fit and well!!
I notice you're using a combination of classes and styles. Have you used styles only where classes won't work in emails?
Cheers Alex, getting there. Certainly when using css internal style tags, there are elements of it that get stripped out by email clients. Inline seems more reliable. Hopefully this video helps folk to understand their options and of course how to build the html for the colour formatting and a vertical table.
Thanks for your fantastic Power Automate videos!
Oh thank you very much Stefan 👍 really appreciated!
Hi.. Your video really helps me in html table styling, i follow yr suggested action, it works. Seeking your advice to include a sort (employee name) expression. i have tried few ways, all resulted in error.
@@berniceleong9148 take a look at learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#sort
Hello Damien, as usual this is a phenomenal resource, I have a question:
The column I am trying to format is a string which contains a % value, I am using your following code if(greater(float(item()?['[Confirmation Ratio (%)]']), 0.5),'style=''background-color: green; color: white''', '') and I am getting the following error: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
Most likely it’s trying to convert null. Null is returned if your expression is wrong or there is no value. You would need to investigate coalesce for null values. learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#coalesce
Welcome back! 😉
Thank you Adi 😍
This is great, I'm struggling a bit to combine this with your previous video, where you get an array of the tables then loop through that based on manager name. In this video you do not use the create html table action, should I just replace the create html table action with the first select from this video. If not what would be the alternative?
I am doing the same thing/have the same issue! I have tried to apply the conditional formatting within the create an HTML set up to try and apply color to specific text; however, it doesn't seem to work similarly. If I use the select function as displayed here for the HTML table, I am not quite clear as to how connect to the rest of the flow to group data based on manager name. Nonetheless, these videos are incredible and I am extremely grateful for them and any assistance you would be able to provide.
Great video, I was playing vertical table setup for a few days, this cleared it up in minutes. Quick question, is it possible to consolidate or merge row values that are the same? My first row is Date. Is it possible to merge the date cells that are the same value? Thanks
By merge, do you mean remove duplicates or almagamate the data for those dates into one row? Union lets you remove duplicates by comparing an array with itself. For more complex arrays you could look at ruclips.net/video/afqvGAb20Dw/видео.html
Hi @DamoBird365 - thank you for your video, I have built 3 beautiful vertical tables, formatting correctly and sending by email with no prior HTML experience! What I am struggling with is how to apply the conditional formatting (if expression) to the 'Concat' function used in the Select action for the vertical table, as it is in the text method rather than the array method.
Can you explain how you would add the IF expression (I need 3 conditions for IF=[Yes, no or N/A]) to row 3 in your vertical table where you have used the concat expression?
Great video! I'm having one issue with the Format Number expression though. For my column that I wanted formatted, I entered the same expression (though I do not need the if statement that you added), but I receive this error: The template language function 'formatNumber' expects its first parameter to be an integer or a decimal number. The provided value is of type 'String'. HOw do I fix that?
Wrap the dynamic expression in int() to convert string to int.
@@DamoBird365 Sorry, I'm a noob at this. Exactly what would that expression be?
Can I use the if expression to exclude the the column if it is blank for a selected row. (I am creating a flow for a selected row to send an email with the html table with values of the row in email body)
I don’t understand how you can exclude a column from a row, unless there’s only one row 🤷♂️ maybe share more details on the forum powerusers.microsoft.com/
Thanks Damien. Is it possible to have some rows left justified and others right justified?
You could try implementing the following www.w3schools.com/css/css_table_align.asp let me know how you get on 👍
Hi -- if you wanted to group by the Company column and add subtotals under another "value" column, could you do this in the compose/css step?
Thanks for the suggestion, if you can send me some sample data, I can add it to my list.
@@DamoBird365 Sorry, but I haven't actually figured it out yet. The flow works ok using the Get-Items/Filter/Create-HTML-Table/Email, just w/ no Subtotals.
Nice one.
How can I include buttons for each row and allow the users to click on it? which updates the item on the sharepoint list
You could use an http triggered flow with a button, check this method from Paul ruclips.net/video/_ytzdybJ8pU/видео.html
Great tutorial, thank you Damien!
Is there a method of adding a Total Row at the end of the table? For example to add up the Amounts in this demonstration. Thank you for the great content.
You could do that. Either on one or many columns. I’ll add it to my list.
@@DamoBird365 That would be great, thank you!
i am looking forward to learn it Sir@@DamoBird365
That if expression is not working while take column object from power bi report itself not any sharepoint.
Can you help me to solve. I want to change background color of entire rows depending on column value.
It should be the same idea. You need an array of data and then write an expression for the key/column that you wish to apply conditional formatting to.
Thanks Damien for the video guidance, i'm having difficulty applying the formatting for both the Row and Cell based on value.
If i run table without the formatting formula for those elements, it runs no problem and table displays the results as expected.
1) If I apply formatting following your guidance to format a row to green where a column = Yes , it returns just the headers of the table.
2) If i remove the formatting from the row and try apply to a cell, the results show but without formatting
Any guidance would be a great help, thanks
Probably worth checking the html with the formatting is valid. You could paste it into w3schools or ask gpt to validate the html.
@@DamoBird365 Thanks alot for pointing in the right direction was going round the houses before, finally managed to get to bottom of it by reviewing the output in W3schools and a bit of trial and error, i eventually identified i'd missed a space between ' style=Background.color as it was returning
@@user-jx2zh3lf2e nice one. Glad you worked it out. 👍
I need help with table designs, i currently use general html designing, and it works great with outlook, however when i send to a gmail account all my styling gets taken away. My understanding is google will only accept inline styling. the issue i am having is that the number of rows is dynamic (sometimes has 1 row sometimes has 10) and i am unable to create inline styling because of that.
I don’t see that as a problem. Select deals with dynamic rows.
I learned a lot on this. Though I can't seem to make the conditional formatting to work.
if(lessOrEquals(item()['Remaining days until expiration'],'0'),'style=''color:#FF0000''','')
Any advise?
You need to check the html, is the logic working? If it is, is the html correct? So check the history and copy the output into something like w3schools to check if the html is valid.
Thanks Damien. I tried doing the background colour for row but it is not working. My expression is - if(equals(mod(int(item()?['Index']),2),1),'style="background-color:lightblue";','') written this expression within May you please have a look into this
Have you copied your html into a wysiwyg editor? Check where it’s not valid? Then you’ll know if it’s your html or your logical expression.
@@DamoBird365 checked on the editor, its coming like this -if(equals(mod(int(item()?['Index']),2),1),'style = "background-color:lightblue";','') and when pasted the same then output is coming like
56
Not able to understand the glitch
Any suggestions please. My source is power bi columns to create html table
Thanks for the valuable work. I will have a request, I know it is possible, but I do not know the method. I want to send an e-mail by adding the person profile picture in the person column in the SharePoint list, which creates an html card. I'm trying to do this somehow, but the profile picture is not showing. Is it possible to do a study on this? thanks
Are you looking to include their profile picture in an HTML table in email? Interesting use case. What's the reason? I agree that it will be possible, but I have not tried myself yet. You could ask on the forum powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
Hi Damien, Amazing Stuff !! Can you tell how to add a dynamic header to the same table based on a data point. It would be really helpful.
Hi there, a dynamic header would work in the same way. You could generate your first row using a dynamic source and bring the header and table rows together in a compose or variable.
@@DamoBird365
So I am trying to get data from Power BI and add a second query like the below and parsing it in Json
EVALUATE
{
FORMAT(
MAX(
'Data Refresh'[Last Refresh (IST):]
), "mmm-dd"
)
}
Then mapping the [Value] to a Date in Select
Then in the last compose you have before sending mail. putting it like this at the top
Data as on: @{body('Select_Date')}
But getting the header like this: Data as on: [{"Date":"Apr-25"}]
I 'm sure I am missing something. Have been at it all day :)
Very nice and I was able to use your way of making a vertical table in my flow. I tried to format cell background, depending on the list data, for example if the cell has "OK" then I need to display it in Green in the table.
I tried adding the conditional inside the tag like below:
........
conditional is: " {item()?['ABCD']}"
I get invalid JSON.
Any advice on how to do the font formatting would be appreciated. Thank you!!
Is your condition inline? You need @ at start of both expressions. Otherwise have you tried using concat?
@@DamoBird365 Thanks so much for your reply. Yes the condition is inline. I'll try adding @ start at expressions. I did not try concat, I'm still learning. There is no need to put the conditional statement inline. If there is another better way to do the same please let me know. Thanks again!
Hi Damien, I tried to insert the conditional statement in the select row like I said before. Per your suggestion,
"
@{item()?['InfraStatus']}"
I did not get any errors. But there is no change in the background color. Any advice will be appreciated. Or if you could give me the exact example of using such a conditional statement within your select row 1 would be great.
@DMK573 my advice would be to get the flow history output of the html and then pace it into a wysiwyg html tool, like w3schools and check the html. Maybe there is a formatting error. It should help you identify the issue.
Damien, I found my mistake and fixed it. Now it's working. I had a space in "Health status", so I removed it. Now all good. Thanks so much for your great video and the suggestion.
I appreciate the guide on html tables, as I have only worked with HTML and CSS styles for the 26 minutes of the video, I wound up here trying to setup an immersive dashboard on teams to view spreadsheets using keywords as custom commands. It doesnt appear that teams likes the formatting through any of the styling which makes it look gross, unfortunate - but I can get past it. The challenge that I'm having is its to track statuses of different orders that are constantly changing.
The inputs into the spreadsheet is done via microsoft forms, then parsed out accordingly. To keep track of the current status I'm using symbols (!, @, #, *, &, +, ~)in their own column, I have a key for which one is which. I would like the html table to reflect each symbol by changing the color of the text for the Order#, but I'm not sure how setting the class for the 7 different conditions can be done. The order will only match one condition at a time, and I'm using keywords on teams to update the symbols.
The way you explained everything in this video was easy to digest, so if theres an answer for my challenge I think you could express it in an easy way to understand.
All I want it to do is look at the other cell that has the symbol and change the text color accordingly, I'm just not sure the best way to do that. Any help would be appreciated
You could try something like this Efficient Power Automate: Replace Complex Switches with JSON Objects
ruclips.net/video/PD980sKKx0E/видео.html for your colours, based them on an object and look up the value based on your symbols, albeit I’ve not tried using symbols as keys in an object. You’ll have to test. Formatting of html in teams doesn’t seem great, adaptive cards might be a good option.
thanks for the quick response and the suggestions, referencing that other video, which made perfect sense in its own right, I'm not entirely sure how to apply it since my input is a "List rows present in table" rather than a single option on a form. The end goal that I'm looking for is making things smoother by not having to swap programs constantly to see all the orders, also makes it easier for management so they dont have to have another spreadsheet to keep track of. Sorry for not making that clearer yesterday as I was in a bit of a rush.
My series of flows are
1) form input -> data dump on a spreadsheet
2) multiple keywords in a switch command to change said symbol based on the orders status (picked [~], released [#], shipped [+[, contact representative [!]). The table is ever-updating since one of the keywords deletes the row from the table
3) keyword [listorders] to pull table from excel and post as html table within teams, without having the spreadsheet open - I want to someway without words to show the statuses
this all started upon learning that power automate cannot see the color of cells - color reflecting status, so I had to find another way to express the status when exporting it to html on teams
Sir, how can i make a table only for the newly created item? I tried adding a Filter Array with ID equal to the ID in my trigger which is the When New Item is Created. The problem is, i have 5 items in my SharePoint List and the flow is sending an email with 5 columns which have the same entries to the newly created one. How do you think i can fix this? Hope you can help me. Thank you
Use get item and the id from the trigger. I think you’re using get items?
@@DamoBird365 yep i used Get Items. I am trying Get Item right now, and then add Filter Array. Question now is what will be my From? value is not in the dynamic content.
@imjohn727 you don’t need to filter if you are looking to get the item that triggered the flow.
@@DamoBird365 oh i see. But how to do it when doing the vertical table but limiting it to the newly created item? I must do a Filter array? Or is there other way to do it?
I'm baffled as to why I can't have two separate words in the header box on "Create HTML table"? Am I doing something wrong? I can otherwise create an HTML table and input it into an email, but can either have only one word headers or a header_with_underscores.
Are they in quotes?
@@DamoBird365 I've tried with and without quotes.
Hi I am trying to format HTML table, which i get from excel table instead of sharepoint like your example
when i use this condition it desont work can you please help
if(equals(item()?['name'],'full team'),'style=''background-color: green'';','')
"name" is my table header, and when its Value is "Full Team" i want to highlight my row green.
i tried this 'FULL TEAM' just in case space was causing the issue but it didnt help
is it because i am pulling data from excel table the if condition doesnt work ?
String comparison is case sensitive. You could use tolower(item()?[‘name’]) to ensure it’s always ‘full team’
@@DamoBird365 Thank you that resolved the issue, i did listen to you in the video to use that, but i was using exact words so thought that might not be the issue, but it is.
thanks again
👍 Liked. My flow triggered: Damien mentioned "repupose" on his video 1 times. ruclips.net/video/LQumCR1B-q0/видео.html
I do love a good repurpose. Do you have an automation running on this?
@@DamoBird365 Me too that's why every time you upload a video my flow gets triggered and counts* how many time you mention repurpose in your video.
*calls an Azure Function and returns the count to Power Automate. I suppose to save those counts in SharePoint and create a ranking of your videos
@ have you thought about sharing the idea with community? Or have you blogged about it? pnp.github.io/
@@DamoBird365 not yet. I can write an article about it if you found that interesting as a use case.