Forum Discussion
Convert decimal to fraction in power query editor
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).
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 swoperator +-×÷()
4 Replies
- ziying35
Impactful Individual
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 swoperator +-×÷()
- edhans
Community Champion
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.
if 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"
- ziying35
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 swMy code only considers cases where the operator is a × sign
