Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Triggers
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
  • Get help with flow
Menu

Format HTML table with SharePoint items in a readable way (Power Automate)

Posted on March 28, 2021January 6, 2022 by Tom

“The HTML table sent in the email from Power Automate is just a mess, its format makes the SharePoint items I’m trying to show really hard to look at in a meaningful way.”


It’s easy to create an HTML table with SharePoint items in a nice, readable format with Power Automate. If you use the ‘Create HTML table’ action, you just input an array with the items into the action and it’ll turn it into an HTML table. Switching to ‘Custom columns’ option you can even define which columns from the SharePoint list should be included.

Once you have the table, you can add it to an email and send it to yourself or your colleagues. But here you’ll notice a small problem for some SharePoint columns, e.g. date with time or multiple choice columns. As you can see on the example below, you wouldn’t call the format of these columns “user friendly”.

Power Automate email with SharePoint items
Create html table

To format the data in a more meaningful way, you’ll need a few expressions.

Format the date

The date uses by default the ISO 8601 format, a useful international standard that’s not easily readable. To display the date in a different way you can use the formatDateTime(…) expression. For example, to display the ‘Modified’ date in ‘dd-MM-yyyy’ format.

formatDateTime(item()?['Modified'],'dd-MM-yyyy')
Power Automate format date in HTML table
Power Automate email with date formatted

Note: ‘Modified’ is the internal name of the date column to process. If the date is not a mandatory field, you might need to exclude the empty dates from the formatDateTime(…) expression.

Format multiple choice columns

If it was a single choice / person column, you’d just select the right dynamic content, but it gets more tricky with multiple choice.

Multiple choice columns give you an array of objects: an array of users or selected choices. In other situations you could use the ‘Select’ action, but it’s not possible in combination with the ‘Create HTML action’. This action wants a direct input, you must do all the processing within a single expression.

And it’s the same expression you use when exporting multiple people picker into a .csv table:

e.g. to get comma delimited list of user email addresses from a column 'Person_MultiplePicker':
join(xpath(xml(json(concat('{"body":{"value":', item()?['Person_MultiplePicker'], '}}'))), '/body/value/Email/text()'), ', ')

or when exporting multiple choice columns into a .csv table:

e.g. to get comma delimited list of choices from a column
join(xpath(xml(json(concat('{"body":{"value":', item()?['Choice'], '}}'))), '/body/value/Value/text()'), ', ')

If you use an expression for all the “problematic” columns, you can create a nice, easily readable HTML table.

Power Automate format multiple choice columns in HTML table
email with SharePoint items readable

Summary

The ‘Create HTML table’ is a great action that can save you a lot of work. Without it you’d have to build an HTML table manually, you’d need a loop and build the table item by item (similar to the approval history solution). But the downside is that it’ll take the full column data.

If you don’t want to show the full data, you might need to preprocess it with the expressions above. Dates with the formatDateTime(…), multiple selection columns with the composed expression, numbers with formatNumber(…), and potentially also link to the SharePoint item.

Just make sure that you won’t be limited by the 100 items limit in ‘Get items’ action.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

2 thoughts on “Format HTML table with SharePoint items in a readable way (Power Automate)”

  1. Sara says:
    May 5, 2021 at 1:25 pm

    Awesome post! You saved me so much headache!

    Reply
  2. travis.jefferds says:
    December 9, 2021 at 7:43 pm

    I knew there had to be a way to make this work, been trying for a day. Thank You providing a huge wave of relief that things can work as they should, though it could have maybe been easier by design haha.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


NEW! Master the HTTP requests to SharePoint with a new cheat sheet!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Use Power Automate to forward Outlook events upon registrationJanuary 29, 2023
  • Why the condition is false for the same numbers (Power Automate)January 25, 2023
  • How to forward event invitation to other calendar (Power Automate)January 22, 2023
  • Run ‘For selected item’ flow from non-default environment (Power Automate)January 18, 2023
  • Hide button in SharePoint list after Power Automate flow startedJanuary 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes