Forum Discussion
What Data Type - multi decimal data.
Hi there,
I have a data source containing numbers separated by multiple decimal places. This is important as this is the format which we use in other systems.
My issue is I am not sure what data type to use to store this information.
The Data might look like this;
2.2
2.2
2.3
2.3
2.3.6.1
2.3.6.2
2.4
When I use data type decimal it comes out like this;
2.2
2.2
2.3
2.3
Error
Error
2.4
Using Text comes out like this;
2.2
2.2
2.99988888888888
2.99988888888888
2.3.6.1
2.3.6.2
2.3999989879
Can anyone advise on which data type I should be using for this?
Thanks in advance.
Simon
SKey,
Try this Power Query solution. The concept is to determine if the original value is number or text; if number, round to the appropriate number of decimal places, otherwise return the original value.
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("i45WMtIzUorVAdHGaLQJjK9npmeIxAaqjwUA", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Column" = _t] ), AddDataTypeColumn = Table.AddColumn( Source, "Data Type", each let result = try Number.From([Original Column]) otherwise "Text", resultType = if result = "Text" then "Text" else "Number" in resultType ), AddNewColumn = Table.AddColumn( AddDataTypeColumn, "New Column", each if [Data Type] = "Number" then Number.Round(Number.FromText([Original Column]), 1) else [Original Column] ), ChangeType = Table.TransformColumnTypes(AddNewColumn, {{"New Column", type text}}) in ChangeType
2 Replies
SKey,
Try this Power Query solution. The concept is to determine if the original value is number or text; if number, round to the appropriate number of decimal places, otherwise return the original value.
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("i45WMtIzUorVAdHGaLQJjK9npmeIxAaqjwUA", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Column" = _t] ), AddDataTypeColumn = Table.AddColumn( Source, "Data Type", each let result = try Number.From([Original Column]) otherwise "Text", resultType = if result = "Text" then "Text" else "Number" in resultType ), AddNewColumn = Table.AddColumn( AddDataTypeColumn, "New Column", each if [Data Type] = "Number" then Number.Round(Number.FromText([Original Column]), 1) else [Original Column] ), ChangeType = Table.TransformColumnTypes(AddNewColumn, {{"New Column", type text}}) in ChangeTypeI'll give this a try and reply with my findings, if you don't mind. Thanks for the possible solution.
Simon
