Forum Discussion
Help with extracting numbers from a string
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
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.
3 Replies
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=EAIaIQobChMI7Za92YSi6wIVFSQrCh0vDgIgEAAYASAAEgLd5_D_BwEHi 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)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.
