Advanced HTML Tables in Power Automate - Formatting cells, rows and vertical tables

Рет қаралды 24,628

Күн бұрын

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 ☕

Пікірлер: 102

@zachbean9589 9 күн бұрын

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!

@FabioSilva-kj3bm Жыл бұрын

Thanks Damien. Today you saved my life with two different videos. I really appreciate it.

@DamoBird365 Жыл бұрын

No problem Fabio, pleased to have helped.

@AmeliaNguyen-fo3gt 2 ай бұрын

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!

@DamoBird365 2 ай бұрын

😍 thank you Amelia

@emmanuelmaceda2475 Жыл бұрын

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!

@DamoBird365 Жыл бұрын

An understanding of select is definitely advantageous for heavy data manipulation. If you’ve got any interesting use cases, drop me a message.

@lukasmillet7255 8 ай бұрын

Amazing tutorial, everything explained perfectly. Thanks!

@DamoBird365 8 ай бұрын

Thank you Lukas.

@365g Жыл бұрын

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 👍

@DamoBird365 Жыл бұрын

Nice one Gerard, good to hear from you and have fun 👍

@mikaeldias5372 3 ай бұрын

I don't want to use 'Create an HTML table' anymore! Thanks a lot!

@DamoBird365 3 ай бұрын

😂👍 sounds like 🫵 are having too much fun 🤩

@andrewjameson5918 Жыл бұрын

That is all brilliant stuff.

@claudiemalette5974 2 ай бұрын

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 😉

@claudiemalette5974 2 ай бұрын

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?

@DamoBird365 2 ай бұрын

Interesting. Could you use emojis? You could try unichar or SVG? Let me know how you get on.

@claudiemalette5974 2 ай бұрын

@@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!

@battle_born 4 ай бұрын

Thank you so much!!! This video saved my sanity 😁

@DamoBird365 4 ай бұрын

Hey no problems 👍

@DaniBored Жыл бұрын

Thank you sir, you saved me! 😊

@DamoBird365 Жыл бұрын

Happy to help 🥳

@alexmcla Жыл бұрын

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?

@DamoBird365 Жыл бұрын

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.

@dexterx9862 4 күн бұрын

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?

@xuanyingren2130 Жыл бұрын

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?

@DamoBird365 Жыл бұрын

line1line2 should work. What string are you replacing on?

@Worrelpa Жыл бұрын

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.

@DamoBird365 Жыл бұрын

Hi Phil, how’s it going? Sounds like the combination of kzfaq.info/get/bejne/hqppa5OksLHcYHk.html and kzfaq.info/get/bejne/pt16apabq6vYdIU.html might help if you can face listening to me for more 😂

@Worrelpa Жыл бұрын

@@DamoBird365 thanks for that Damien

@meljovanovic9534 2 күн бұрын

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?

@dblumenstetter 4 ай бұрын

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

@DamoBird365 4 ай бұрын

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 kzfaq.info/get/bejne/l8yhqapyx5iUdKs.html

@hugarohugaro Жыл бұрын

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.'.

@DamoBird365 Жыл бұрын

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

@berniceleong9148 13 күн бұрын

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.

@DamoBird365 12 күн бұрын

@@berniceleong9148 take a look at learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#sort

@AdiCristea Жыл бұрын

Welcome back! 😉

@DamoBird365 Жыл бұрын

Thank you Adi 😍

@ajambo2010 6 ай бұрын

Thanks Damien. Is it possible to have some rows left justified and others right justified?

@DamoBird365 6 ай бұрын

You could try implementing the following www.w3schools.com/css/css_table_align.asp let me know how you get on 👍

@menhardbesho Жыл бұрын

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.

@DamoBird365 Жыл бұрын

You could do that. Either on one or many columns. I’ll add it to my list.

@menhardbesho 11 ай бұрын

@@DamoBird365 That would be great, thank you!

@nguyenmanhtuan1772 10 ай бұрын

i am looking forward to learn it Sir@@DamoBird365

@ajibadeoluwaseni7020 Жыл бұрын

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

@DamoBird365 Жыл бұрын

You could use an http triggered flow with a button, check this method from Paul kzfaq.info/get/bejne/ld-krceqx7CcoIk.html

@tommymartin2089 5 ай бұрын

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.

@DamoBird365 5 ай бұрын

I don’t see that as a problem. Select deals with dynamic rows.

@James-yl6mj 2 ай бұрын

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)

@DamoBird365 2 ай бұрын

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/

@stefankirst3234 Жыл бұрын

Thanks for your fantastic Power Automate videos!

@DamoBird365 Жыл бұрын

Oh thank you very much Stefan 👍 really appreciated!

@eaugust1205 10 ай бұрын

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?

@DamoBird365 10 ай бұрын

Wrap the dynamic expression in int() to convert string to int.

@eaugust1205 10 ай бұрын

@@DamoBird365 Sorry, I'm a noob at this. Exactly what would that expression be?

@stevebrown5720 11 ай бұрын

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?

@DamoBird365 11 ай бұрын

Thanks for the suggestion, if you can send me some sample data, I can add it to my list.

@stevebrown5720 11 ай бұрын

@@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.

@SabyasachiGoswami09 Жыл бұрын

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.

@DamoBird365 Жыл бұрын

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.

@SabyasachiGoswami09 Жыл бұрын

@@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 :)

@ylmazozturk06 11 ай бұрын

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

@DamoBird365 11 ай бұрын

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

@marychristeleugenio8126 Жыл бұрын

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?

@DamoBird365 Жыл бұрын

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.

@harshitasrivastava8686 3 ай бұрын

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

@DamoBird365 3 ай бұрын

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.

@harshitasrivastava8686 3 ай бұрын

@@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

@harshitasrivastava8686 3 ай бұрын

Any suggestions please. My source is power bi columns to create html table

@user-jx2zh3lf2e 4 ай бұрын

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

@DamoBird365 4 ай бұрын

Probably worth checking the html with the formatting is valid. You could paste it into w3schools or ask gpt to validate the html.

@user-jx2zh3lf2e 4 ай бұрын

@@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

@DamoBird365 4 ай бұрын

@@user-jx2zh3lf2e nice one. Glad you worked it out. 👍

@souravdutta3326 Жыл бұрын

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.

@DamoBird365 Жыл бұрын

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.

@DMK573 7 ай бұрын

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!!

@DamoBird365 7 ай бұрын

Is your condition inline? You need @ at start of both expressions. Otherwise have you tried using concat?

@dammikakahanda79 7 ай бұрын

@@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!

@DMK573 7 ай бұрын

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.

@DamoBird365 7 ай бұрын

@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.

@DMK573 7 ай бұрын

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.

@Scott-yu4bk Жыл бұрын

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.

@Scott-yu4bk Жыл бұрын

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

@DamoBird365 Жыл бұрын

You could try something like this Efficient Power Automate: Replace Complex Switches with JSON Objects kzfaq.info/get/bejne/hqppa5OksLHcYHk.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.

@Scott-yu4bk Жыл бұрын

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

@imjohn727 9 ай бұрын

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

@DamoBird365 9 ай бұрын

Use get item and the id from the trigger. I think you’re using get items?

@imjohn727 9 ай бұрын

@@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.

@DamoBird365 9 ай бұрын

@imjohn727 you don’t need to filter if you are looking to get the item that triggered the flow.

@imjohn727 9 ай бұрын

@@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?

@samanthavalentine671 Жыл бұрын

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.

@DamoBird365 Жыл бұрын

Are they in quotes?

@samanthavalentine671 Жыл бұрын

@@DamoBird365 I've tried with and without quotes.

@technicaltrainer5627 Жыл бұрын

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&nbspTEAM' 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 ?

@DamoBird365 Жыл бұрын

String comparison is case sensitive. You could use tolower(item()?[‘name’]) to ensure it’s always ‘full team’

@technicaltrainer5627 Жыл бұрын

@@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. kzfaq.info/get/bejne/greloKaDlqiRoWQ.html

@DamoBird365 Жыл бұрын

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

@DamoBird365 Жыл бұрын

@ 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.