cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leu0101
Helper III
Helper III

Get items then format number to remove decimal using select before creating a HTML Table for email

I am currently having issues with formatting a number column used for percentages. 0 - 100%. I have the column formatted to not use the decimal in the SharePoint list. However in the power automate flow it uses the decimal. My issue is either formatting numbers with for all whether that be an expression or using the apply to each so I can apply it to "Select" before adding it into the Create HTML Table to show a percentage without a decimal. Any help would greatly be appreciated. I didn't see much out there for applying format number to apply to each when getting items using the select function.

FormatNumber.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User
Super User

Hi @Leu0101,

 

One of your items in the list has an empty percentage complete column.

 

The current expression cannot handle that. It tries to format a value which does not exist. If you want to take empty values into account you could add a check for empty into the existing expression.

 

Below is an example of that:

if(empty(string(item()?['PercentageComplete'])), '0', formatnumber(item()?['PercentageComplete'], '0'))

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


View solution in original post

4 REPLIES 4
Expiscornovus
Super User
Super User

Hi @Leu0101,

 

Like your screenshot already suggests, you could use a formatnumber function with the Zero placeholder format in the Select.

 

Try something like the example below.

You might need to change the name of the column, I guessed your column name 😁

 

 

formatnumber(item()['PercentageComplete'], '0')

 

 

formatnumber_zero.png

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


I gave it a shot but it looks like I am encountering a error. I could only assume that maybe my column is formatted incorrectly? I used:

formatnumber(item()['Percent_x0020_Complete'], '0') as the expression.
However, I was met with "The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"Title": "@item()?['Title']",
"Submitter": "@item()?['Submitter']",
"Description": "@item()?['Description']",
"Priority": "@item()?['Priority/Value']",
"Assigned To": "@item()?['Assignedto/DisplayName']",
"Percentage Complete": "@formatnumber(item()['Percent_x0020_Complete'], '0')",
"Notes": "@item()?['SharedNotes']"
}' failed: 'The template language expression 'formatnumber(item()['Percent_x0020_Complete'], '0')' cannot be evaluated because property 'Percent_x0020_Complete' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID, Title, DateReported, Priority, Priority#Id, Status, Status#Id, Modified, Created, Author, Author#Claims, Editor, Editor#Claims, Description, DaysOld, Submitter, {Identifier}, {IsFolder}, {Thumbnail}, {Link}, {Name}, {FilenameWithExtension}, {Path}, {FullPath}, {HasAttachments}, {VersionNumber}'. Please see https://aka.ms/logicexpressions for usage details.'."
 
However, my column does exist as a number column in my SharePoint list.
Expiscornovus
Super User
Super User

Hi @Leu0101,

 

One of your items in the list has an empty percentage complete column.

 

The current expression cannot handle that. It tries to format a value which does not exist. If you want to take empty values into account you could add a check for empty into the existing expression.

 

Below is an example of that:

if(empty(string(item()?['PercentageComplete'])), '0', formatnumber(item()?['PercentageComplete'], '0'))

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Leu0101
Helper III
Helper III

Ahh that's what it is! Thank you for your help! Much appreciated!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,661)