What Data Type - multi decimal data.

[SKey]

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

[DataInsights · Accepted solution]

@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

[SKey]

@DataInsights 

I'll give this a try and reply with my findings, if you don't mind. Thanks for the possible solution. 

Simon