Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
ziying35
Impactful Individual
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 sw

 

operator +-×÷()

View solution in original post

4 REPLIES 4
ziying35
Impactful Individual
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 sw

 

operator +-×÷()

ziying35
Impactful Individual
Impactful Individual

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors