Power Query transformation

[Burhan_Jiru]

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

[CNENFRNL]

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

[AlienSx]

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]

Hello,

thank you for your help.

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

[AlienSx]

then replace "s" line with 

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

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

[AlienSx · Accepted solution]

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