Convert decimal to fraction in power query editor

[Anonymous]

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).

[ziying35 · Accepted 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 +-×÷()

[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 sw

My code only considers cases where the operator is a × sign

[edhans]

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.

[Anonymous]

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"