Extract specific value from row into new column

Hi Mooihoek ,

In Power Query, you can go to Add Column tab > Extract > Text After Delimeter. Use "Amount: " as the delimeter (quotes not included).  You can then Replace the comma with nothing and convert the new column to Decimal Number type.

Paste the following M-Script in the advanced editor of a  blank query in  Query Editor for the sample.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4tKMgvKlHwK81NSi1SULZSMDQ0NDIyMTGNyYvJ83QB8g0MzS0sLID8YH+QvKmZuYWlhaWBRUyeY25+aV6JlYKZgY6FsbmepVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Data], "Amount: "), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter",",","",Replacer.ReplaceText,{"Text After Delimiter"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Text After Delimiter", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Text After Delimiter", "Amount"}})
in
    #"Renamed Columns"

Try this also ... if danextian solution does not fit your needs!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4tKMgvKlHwK81NSi1SULZSMDQ0NDIyMTFVitWJVvJ0AQoYGJpbWFhABIL9QWpMzcwtLC0sDSzAYo65+aV5JVYKZgY6FsbmepZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Column1.1]), "Column1.1", "Column1.2")
in
    #"Pivoted Column"

Say, if you remove the Pivotd column step in the above code, you may see it as 

Hi Mooihoek ,

In case you want to do this in DAX:

Column 2 =
RIGHT (
    'Table'[Column1],
    LEN ( 'Table'[Column1] ) - SEARCH ( "amount:", 'Table'[Column1] )
        - LEN ( "amount:" )
)

Best Regards,

Jay