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

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

 

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

 

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

 

Leu0101
Helper I
Helper I

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

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,460)