[BUG] Custom Column Number.ToText Does Not Convert Unless SELECT Statement in SQL Is Used

[hxwt]

I have two Queries in the same report, accessing the same SQL table - one uses SELECT * FROM [Table] statement under Advanced Options to access the data, and the other uses [Database]{[Schema="dbo",Item="[Table]"]}[Data]. Both have a custom column added using a [Result] column typed as a decimal number.

#"Added Custom" = Table.AddColumn(Source, "Custom", each Number.ToText([Result], "x"))

The SELECT table results form as expected, with a custom column containing the decimal number in [Result] coverted to hexadecimal format. The [Database]{[Schema table has a custom column, but it's just the [Result] number without conversion. It doesn't seem to be just a UI bug - when I apply the changes, the data in the table remains unconverted. Is this reproducable for anyone else?

[v-jingzhang · Accepted solution]

Hi @hxwt 

I can reproduce the same result at my side. The workaround is to add a step before adding the custom column to convert the [Result] column from Decimal Number data type to Whole Number type. This can get the column converted correctly. You may have a try. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.