Help with extracting numbers from a string

[timknox]

Please can someone help me!

I have a data source (attached) and one of the columns has prices.

The problem i have is that in the string there can be up to about 10 individual prices that need to be added together to give the overall price.

For example in line 65, the string is:

a:6:{i:0;a:3:{s:8:"Quantity";s:2:"38";s:4:"Rate";s:3:"3.2";s:8:"NetPrice";d:121.6000000000000085265128291212022304534912109375;}i:1;a:3:{s:8:"Quantity";s:2:"38";s:4:"Rate";s:4:"2.10";s:8:"NetPrice";d:79.7999999999999971578290569595992565155029296875;}i:2;a:3:{s:8:"Quantity";s:2:"38";s:4:"Rate";s:3:"2.1";s:8:"NetPrice";d:79.7999999999999971578290569595992565155029296875;}i:3;a:3:{s:8:"Quantity";s:2:"38";s:4:"Rate";s:3:"2.1";s:8:"NetPrice";d:79.7999999999999971578290569595992565155029296875;}i:4;a:3:{s:8:"Quantity";s:2:"38";s:4:"Rate";s:3:"3.2";s:8:"NetPrice";d:121.6000000000000085265128291212022304534912109375;}i:5;a:3:{s:8:"Quantity";s:2:"38";s:4:"Rate";s:3:"2.1";s:8:"NetPrice";d:79.7999999999999971578290569595992565155029296875;}}

What i am looking for is a calculated column that would add together all the 'NetPrice' figures higlighted in RED - so the answer should be 562.40

I am not if this is possible, but i thought i would ask.

Thanks in advance

[pranit828 · Accepted solution]

Hi @timknox 

I took the same file an d created th below M code on Power Query Editor gave me the exact output you are looking for.

Below is what I did, Create a new table, name it Table_longText.

Name the column Long_text. Check all the details are correct as marked in red below.

Insert the Json code(provided as an example) as the data in Long_Text column.

You should have a table 'Table_longText' created by now.

Go to the advance editor and pas the below code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZIxDsIwDEXvkgNEjlMnsXsHBKxthwg6ZGGAMKCqdycpSwcWEBK1l/9tWXr6ctepKE6mJNBGsTLdJEivDvd4ySk/etXeBMvAhkU2RR5jHhdj61zjouvRbsz7azrV5VkMGu1gXYHQkcGAXHYIiBYask11wNZTOycxH0NUg9rAewrP2vO6vCFfCIAcU2lGKkxEgIzswgsCv0qiQPyQwW6AodnCR9Dfg5jVMDwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Long_Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Long_Text", type text}}),
    #"Split Column by Positions" = Table.SplitColumn(#"Changed Type", "Long_Text", Splitter.SplitTextByPositions({76, 126, 200, 249, 322, 371, 444, 493, 566, 616, 689, 738}), {"Long_Text.1", "Long_Text.2", "Long_Text.3", "Long_Text.4", "Long_Text.5", "Long_Text.6", "Long_Text.7", "Long_Text.8", "Long_Text.9", "Long_Text.10", "Long_Text.11", "Long_Text.12"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Long_Text.1", type text}, {"Long_Text.2", type text}, {"Long_Text.3", type text}, {"Long_Text.4", type text}, {"Long_Text.5", type text}, {"Long_Text.6", type text}, {"Long_Text.7", type text}, {"Long_Text.8", type text}, {"Long_Text.9", type text}, {"Long_Text.10", type text}, {"Long_Text.11", type text}, {"Long_Text.12", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Long_Text.2", "Long_Text.4", "Long_Text.6", "Long_Text.8", "Long_Text.10", "Long_Text.12"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Long_Text.11", type number}, {"Long_Text.9", type number}, {"Long_Text.7", type number}, {"Long_Text.5", type number}, {"Long_Text.3", type number}, {"Long_Text.1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Total", each [Long_Text.1]+[Long_Text.3]+[Long_Text.5]+[Long_Text.7]+[Long_Text.9]+[Long_Text.11])
in
    #"Added Custom"

You will get the below output in this case.

Now you can hide the other columns other than the total as per your need.

[harshnathani]

Hi @timknox ,

See if these posts help you

https://community.powerbi.com/t5/Desktop/Extract-number-and-text-from-string/m-p/1190132

https://community.powerbi.com/t5/Desktop/Extract-the-number-and-text-from-String/m-p/399848

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@timknox , seem like json data. refer if this work

https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...