Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
I'll give this a try and reply with my findings, if you don't mind. Thanks for the possible solution.
Simon
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |