Forum Discussion
Solved
Insert Decimal
Good morning! I have a whole number column, and I'd like to insert a decimal after the second digit. How would I go about doing this? Example below. Currently the number is: 12345678 I'd like the number to be: 12.345678 Also something the number is 0. If 0 i just want it to stay 0 (if possible). Thank you!
ThisIsIt - Perhaps:
DAX Column = IF([Column] = 0,0,[Column]/1000000) Power Query Column = if [Column] = 0 then 0 else [Column]/1000000
4 Replies
Hi ThisIsIt
if the length of the whole number differs you can do it with Power Query like this:
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7dQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Start([Value],2) & "." & Text.End([Value],Text.Length([Value])-2)), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}) in #"Changed Type"or you can do it with DAX:
Decimal Number = CONVERT ( LEFT ( CONVERT ( 'Table (2)'[Value], STRING ), 2 ) & "." & RIGHT ( CONVERT ( 'Table (2)'[Value], STRING ), LEN ( CONVERT ( 'Table (2)'[Value], STRING ) ) - 2 ), DOUBLE )A shorter version of the above DAX formula is:
Decimal Number = CONVERT ( LEFT ( 'Table (2)'[Value], 2 ) & "." & RIGHT ( 'Table (2)'[Value], LEN ('Table (2)'[Value] ) - 2 ), DOUBLE )Regards FrankAT
Thank you all for you quick replies and suggestions. They all work for me. Really appreicate it!
ThisIsIt ,
create a number like [column]/1000000 , change data type to decimal with 6 decimal place
12345678/1000000
ThisIsIt - Perhaps:
DAX Column = IF([Column] = 0,0,[Column]/1000000) Power Query Column = if [Column] = 0 then 0 else [Column]/1000000
