cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pandaym
Level: Powered On

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
Community Support Team
Community Support Team

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
Thomas_B
Level: Powered On

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),
    ", "
)
Community Support Team
Community Support Team

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.
pandaym
Level: Powered On

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

Community Support Team
Community Support Team

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.
pandaym
Level: Powered On

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

 

Community Support Team
Community Support Team

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
pandaym
Level: Powered On

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

Works perfectly, thank you!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,055)