Forum Discussion

Burhan_Jiru's avatar
Burhan_Jiru
Frequent Visitor
3 years ago
Solved

Power Query transformation

Hi there,

I have to change this into integers and add zeros according to Billion and million.

any help or suggestions are appreciated.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you

Need Help
Show and Tell
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, Burhan_Jiru 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jbWMzR1UorVATItDcx9wSxdc2M9IyAzFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    power = [M = 6, B = 9],
    f = (t as text) => Number.From(Text.TrimEnd(t, Record.FieldNames(power))) * Number.Power(10, Record.Field(power, Text.End(t, 1))),
    s = Table.TransformColumns(Source, {"Column1", f})
in
    s

     

     

     

     

5 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Burhan_Jiru 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jbWMzR1UorVATItDcx9wSxdc2M9IyAzFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    power = [M = 6, B = 9],
    f = (t as text) => Number.From(Text.TrimEnd(t, Record.FieldNames(power))) * Number.Power(10, Record.Field(power, Text.End(t, 1))),
    s = Table.TransformColumns(Source, {"Column1", f})
in
    s

     

     

     

     

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    or this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jbWMzR1UorVATItDcx9wSxdc2M9IyAzFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    power = [M = "e6", B = "e9"],
    f = (t as text) => 
        Number.From(
                Text.Remove(t, Record.FieldNames(power)) & Record.Field(power, Text.End(t, 1))
        ),
    s = Table.TransformColumns(Source, {"Column1", f})
in
    s
    • Burhan_Jiru's avatar
      Burhan_Jiru
      Frequent Visitor
      3 years ago

      Hello,

      thank you for your help.

       

      I am adding a custom column with this m code but getting a table instead of values.

      • AlienSx's avatar
        AlienSx
        Super User
        3 years ago

        then replace "s" line with 

        s = Table.AddColumn(Source, "Amount", each f([Column1]))

        This adds a new column "Amount" to table Source. 

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    3 years ago
    let
    Sci = [M = "e6", B = "e9"],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jbWMzR1UorVATItDcx9wSxdc2M9IwjT0EjP2EQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    Transformed = Table.AddColumn(Source, "Num", each Expression.Evaluate(Text.Select([Column1], {"0".."9","-","."}) & (try Record.Field(Sci, Text.End([Column1],1)) otherwise "")))
in
    Transformed