cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Remove decimals on table values (Multiple value column from SharePoint List)

Hi,


I am having problems getting friendly numbers out of a SharePoint list where a columns contains multiple values, that are numbers.


I want to use this to specificy a set of values that are true for each item. The formula I use is:

Concat(ThisItem.Capacity,Value,", ") but unfortunately this gives me: 

6.00000000000000, 8.00000000000000, 10.0000000000000, 12.0000000000000, 16.0000000000000, 20.0000000000000, 25.0000000000000, 35.0000000000000

 

I cannot use Round() as that only accepts numbers, and the output of the Concat is table.

 

Can anyone help?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Hi @pandaym ,

If you want to remove the last the comma from the concat string, please take a try with the following formula:

Left(
Concat(ThisItem.Capacity, Round(Value(Value),0) & ", "),
Len(Concat(ThisItem.Capacity, Round(Value(Value),0) & ", "))-2
)

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Resolver II
Resolver II

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Hi @pandaym 

 

Have you tried to use the Round() function this way:

 

Concat(
    Round(ThisItem.Capacity,Value),
    ", "
)
Highlighted
Community Support
Community Support

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Hi @pandaym ,

Could you please share a bit more about the Capacity column in your SP list? Is it a Choice type column?

I have made a test on my side, and don't have the issue that you mentioned.

If you want to remove the decimals on table values (on your side, it is ThisItem.Capacity), please take a try with the following workaround:5.JPG

Set the Text property of the Label control within the Gallery to following formula:

Concat(ThisItem.Capacity, Round(Value(Value),0) & "; ")

 

On your side, you should type following:

Concat(ThisItem.Capacity, Round(Value(Value),0) & ", ")

 

More details about Round() function, please check the following article:

Round function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Tried that variation. Unfortunately I get errors.

 

It again says it expects a number value but receives a table for ThisItem.Capacity.

round1.jpg

Highlighted
Community Support
Community Support

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Hi @pandaym ,

Could you please share more details about your Concat formula?

Based on the screenshot that you mentioned, I think there is something wrong with your Concat function.

Please consider take a try with the following workaround:

Set the Text property of the Label control within your Gallery to following:

Concat(
ThisItem.Capacity,
Round(Value(Value),0) & ", " /* <-- Use Round function here, rather than as you mentioned */
)

Note: Do not put ThisItem.Capacity formula as the first argument of the Round() function. In addition, do not use Round() formula as the first argument of the Concat function.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Remove decimals on table values (Multiple value column from SharePoint List)

My capacity column is a look-up column with "Allow multiple values". The list this is used on is a list where we define all our products, and assign the capacities each product comes in.

 

The list it does look up to is just a single column list where all our available capacities are listed as number values.

 

Your formula works, thank you! Just one thing that is annoying is it adds a comma after the last value.

 

So for the following formules I get:

 

Concat(ThisItem.Capacity, Round(Value(Value),0) & ", ") 
6, 8, 10, 12, 16, 20, 25, 35,

 

Concat(ThisItem.Capacity,Value,", ")

6.00000000000000, 8.00000000000000, 10.0000000000000, 12.0000000000000, 16.0000000000000, 20.0000000000000, 25.0000000000000, 35.0000000000000

 

Can I get this instead?

6, 8, 10, 12, 16, 20, 25, 35

 

Highlighted
Community Support
Community Support

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Hi @pandaym ,

If you want to remove the last the comma from the concat string, please take a try with the following formula:

Left(
Concat(ThisItem.Capacity, Round(Value(Value),0) & ", "),
Len(Concat(ThisItem.Capacity, Round(Value(Value),0) & ", "))-2
)

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Remove decimals on table values (Multiple value column from SharePoint List)

Works perfectly, thank you!

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,408)