Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How do I change or create a new column of measurements that appear as decimal numbers (4.5 x 2.5 x5.5) to appear as fractions instead (4 1/2 x 2 1/2 x 5 1/2).
Solved! Go to Solution.
@Anonymous
// out
let
sor = "2.5×(0.6+2.7)-7.32÷2.42",
zip = let decs = List.RemoveItems(Text.SplitAny(sor, "+-×÷()"),{""}), frs=List.Transform(decs, fx) in List.Zip({decs,frs}),
res = List.Accumulate(zip, sor, (s,c)=>Text.Replace(s,c{0},c{1}))
in
res
// fx
(decstr)=>
let
spn = Text.Split(decstr,"."),
sw = if spn{1}? = null then spn{0} else
let
len = Text.Length(spn{1}),
d = Number.From(len),
m = let a=Number.From(spn{1}),
b=Number.Power(10,d),
p=List.Accumulate({1..List.Min({a, b})}, 1, (s,c)=>if Number.Mod(a, c)=0 and Number.Mod(b, c)=0 then c else s)
in if spn{0}="0" then Text.Format("#{1}/#{2}",{spn{0}, a/p, b/p}) else Text.Format("#{0} #{1}/#{2}",{spn{0}, a/p, b/p})
in
m
in sw
operator +-×÷()
@Anonymous
// out
let
sor = "2.5×(0.6+2.7)-7.32÷2.42",
zip = let decs = List.RemoveItems(Text.SplitAny(sor, "+-×÷()"),{""}), frs=List.Transform(decs, fx) in List.Zip({decs,frs}),
res = List.Accumulate(zip, sor, (s,c)=>Text.Replace(s,c{0},c{1}))
in
res
// fx
(decstr)=>
let
spn = Text.Split(decstr,"."),
sw = if spn{1}? = null then spn{0} else
let
len = Text.Length(spn{1}),
d = Number.From(len),
m = let a=Number.From(spn{1}),
b=Number.Power(10,d),
p=List.Accumulate({1..List.Min({a, b})}, 1, (s,c)=>if Number.Mod(a, c)=0 and Number.Mod(b, c)=0 then c else s)
in if spn{0}="0" then Text.Format("#{1}/#{2}",{spn{0}, a/p, b/p}) else Text.Format("#{0} #{1}/#{2}",{spn{0}, a/p, b/p})
in
m
in sw
operator +-×÷()
Hi, @Anonymous
// out
let
sor = "2.5×0.6×7.32",
split = Text.Combine(List.Transform(Text.Split(sor, "×"), fx), "×")
in
split
// fx
(decstr)=>
let
spn = Text.Split(decstr,"."),
sw = if spn{1}? = null then spn{0} else
let
len = Text.Length(spn{1}),
d = Number.From(len),
m = let a=Number.From(spn{1}),
b=Number.Power(10,d),
p=List.Accumulate({1..List.Min({a, b})}, 1, (s,c)=>if Number.Mod(a, c)=0 and Number.Mod(b, c)=0 then c else s)
in if spn{0}="0" then Text.Format("#{1}/#{2}",{spn{0}, a/p, b/p}) else Text.Format("#{0} #{1}/#{2}",{spn{0}, a/p, b/p})
in
m
in sw
My code only considers cases where the operator is a × sign
You would need to do the math yourself, and then show it as a string. There is no fraction format in Power BI. You cannot even use the # ?/? custom fraction fromat from Excel. See this article if you want to tackle the logic in Power Query.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingif this approach is suitable for solving your problem, you don't need arithmetic 😀, but only some vocabulary
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtEzVahQMAKRpnrmpkqxOtFK5hCeEZAXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
arith=[5=" 1/2",75=" 3/4",25=" 1/4"],
#"Added Custom" = Table.AddColumn(#"Changed Type", "frac", each Text.Combine( List.Transform(Splitter.SplitTextByAnyDelimiter({"."," "})(_[Column1]),each Record.FieldOrDefault(arith,_," "&_))))
in
#"Added Custom"