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
Dual Super User
Dual 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
Dual Super User
Dual 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
Dual Super User
Dual 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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,151)