Certain number format not getting preserved when unpivoting

1

Hello,

I’m encountering a problem where certain number formatting is not preserved after doing an unpivot.

For example, for Id “3kwes”, before unpivoting the value of is “8”:

image

However, after unpivoting, the value becomes “8.0”:

image

It’s strange because it is only happening with 8, which before the unpivot was also in another column as “8.0”. So the number 7 is not affected.

I attach this sample project:

Unpivot format change.morph (2.1 KB)

Thanks very much,

Roberto

2

This is acceptable behavior when no column formatting is applied. If you need a particular number format enforced, use the column formatting setting.

3

Hello Dmitry,

At what point should I do the column formatting setting? When you do an unpivot, you are putting in the same column values of different columns, some could be number, some text etc. So I can’t just format the whole column to one specific format. I need to preserve both the 8 and the 8.0, because later on I have to do a pivot action to go back to the original table before the unpivoting. When I do a data import into a database, Field2 does not accept “8.0”, it only accepts “8”, while Field1 expects “8.0”.

Why can’t it keep the same value as before unpivoting?

Thanks,

Roberto

4

The value is the same. The presentation may differ.

I would suggest ignoring formatting of unpivoted data and only establish formatting when data is pivoted back.

5

My process is inside an iteration where the column names of this table change in each iteration. I can’t have an action to change the format of a certain column (because that column will not be there in each iteration, so the process will fail.

How can I preserve exact formatting after unpivoting and pivoting, and make this process universal so that it works for all my iterations, regardless of the column name?

And what’s the reason behind the change from 8 to 8.0? Just curious.

Thanks,

Roberto

6

Some internal logic with decimal numbers in .NET, we don't control that.

Try deriving a table from the source table and removing all rows from it to make it empty. Then append the result table to the empty table. When two tables are appended, the formats of the 1st table are preserved.