cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asamr
Helper I
Helper I

Extract numbers from string and SUM them

Dear PBI Community,

 

I have this long meta-data string.

 

In this string "Price":495 and "Price":12136 is occuring.

 

Im searching for a PowerQuery formula that can extract the numbers after price and sum then.

 

Any idea how to solve this?

 

 

{"adults":"2","children":"0","infants":"0","guest":{"first_name":"urban","last_name":"Lindqvist","email":"mail@outlook.com"},"addons":[{"code":"2-1-16","price":495,"quantity":1,"inventory_code":"31351"},{"code":"2-1-10","quantity":1,"inventory_code":"98892"}],"price":12136}

 

 
4 ACCEPTED SOLUTIONS
Rocco_sprmnt21
Super User
Super User

 

Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(Text.SplitAny(Text.Lower([Column1]),Text.Combine({":".."}"}&{" ".."/"})), Number.From)))

 

View solution in original post

Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(List.Transform(List.Skip(Text.Split([Column1],"price"":")), (t)=> Text.BeforeDelimiter(t,",") ), (tt)=>Number.From(Text.Split(tt,"}"){0}))))

View solution in original post

Table.AddColumn(Origine, "Personalizzato", each List.Sum(List.Transform(List.Transform(List.Skip(Text.Split([Colonna1],"price"":")), (t)=> Text.BeforeDelimiter(t,",") ), (tt)=>Number.From(Text.Split(tt,"}"){0},"en-US"))))

View solution in original post

15 REPLIES 15
Rocco_sprmnt21
Super User
Super User

 

this should be general enough to handle some of the possible cases

 

 Table.AddColumn(Origine, "pr1", each List.Sum(List.Transform(List.Skip(Text.Split([Colonna1],"price"":")),     
    (t)=> try Expression.Evaluate(Text.BeforeDelimiter(t,",") &"*"& Text.BeforeDelimiter(Text.AfterDelimiter(t, """quantity"":"),"}")) otherwise Number.FromText(Text.BeforeDelimiter(t,",") ))))

 

This data is terrible. WHEN Quantity occurs it is always the price first then Quantity.
There is no Quantity after the last price. Last time we also added - Because it can be decimal numbers, I don't see that part in the latest formula.

 

Last time we added {0},"en-US")))) - Because of the decimal numbers. Does this handle decimal numbers or does it needs to be tweaked in the last part?

"last time", for me, means a lot of problems ago.

You had to be the one to propose an example with decimal numbers and, perhaps, I could have thought of a valid solution also for these cases.

 

trry this

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY4xCsMwDEWvYjQb4wbLVnoVK0NwPXiI0xa3kMF3rzoESuhQirYH+u/FCAxIAUcKDEqTJ60YrveSMsPZDWjQaoZSnzmtlzdjSOjkGITfHnNtpW3Ch971x+foyYQgJK3LkmuTdZh03GVfVR7/UZ2stcb9WIldHSIJD5HTCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "pr1", each List.Sum(List.Transform(List.Skip(Text.Split([Colonna1],"price"":")),     
    (t)=> try Expression.Evaluate(Text.BeforeDelimiter(t,",") &"*"& Text.BeforeDelimiter(Text.AfterDelimiter(t, """quantity"":"),"}")) otherwise Number.FromText(Text.BeforeDelimiter(t,","),"en-US" ))))
in
    #"Aggiunta colonna personalizzata"

 

Hi Rocco

The above did not work - Now Im gonna provide as much information as possible.

My Source is this: 
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\asra\OneDrive - x a-s\PowerBI\xClub\revenue.json"), null, null)}),

Here is sample data:

  • Decimal numbers
  • Minus Quantity
  • Quantity

 

These should every possible case of data and should provide the most optimal results, if you are able to crack it. 

 

Thank you for your time and help. This could never have been done without your contribution. 
I am sorry for not providing everything in the beginning, I also learned all these different scenarios a long the way.

 

["13 01 2012", 43313, 41, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"abc def\",\"last_name\":\"abc\",\"email\":\"x@stofanet.dk\",\"phone\":\"11111111\",\"customer_id\":\"451831\",\"club_id\":\"BjsT\"},\"addons\":[{\"code\":\"3-1-21143378\",\"price\":65,\"inventory_code\":\"80102\",\"quantity\":2},{\"code\":\"3-1-21143379\",\"price\":60,\"inventory_code\":\"80104\",\"quantity\":2}],\"price\":1400,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

["31 05 2017", 43313, 63, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x x\",\"last_name\":\"x\",\"email\":\"x@stofanet.dk\",\"phone\":\"11223344\",\"customer_id\":\"451831\",\"club_id\":\"BjsT\"},\"price\":998,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

["30 10 2011", 43315, 81, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@storegade3.dk\",\"phone\":\"11223344\",\"customer_id\":\"661060\",\"club_id\":\"B-IC\"},\"addons\":[{\"code\":\"2-1-39\",\"price\":75,\"inventory_code\":\"9971\",\"quantity\":1},{\"code\":\"3-1-20499510\",\"price\":595,\"inventory_code\":\"c1070\",\"quantity\":1},{\"code\":\"3-1-20499522\",\"price\":595,\"inventory_code\":\"c1072\",\"quantity\":1},{\"code\":\"3-1-20499559\",\"price\":595,\"inventory_code\":\"c1072\",\"quantity\":1},{\"code\":\"3-1-20499682\",\"price\":595,\"inventory_code\":\"c1070\",\"quantity\":1},{\"code\":\"3-1-20499702\",\"price\":595,\"inventory_code\":\"c1070\",\"quantity\":1},{\"code\":\"3-1-20499513\",\"price\":595,\"inventory_code\":\"c1067\",\"quantity\":1},{\"code\":\"3-1-20499533\",\"price\":595,\"inventory_code\":\"c1067\",\"quantity\":1},{\"code\":\"3-1-20499560\",\"price\":595,\"inventory_code\":\"c1067\",\"quantity\":1},{\"code\":\"3-1-20499622\",...

["01 08 2012", 42587, 569, 5, "{\"adults\":2,\"children\":1,\"infants\":1,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@mail.dk\",\"phone\":\"11224455\",\"customer_id\":\"741525\",\"club_id\":\"sl_65182\"},\"price\":1487.5,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

["13 11 2013", 42589, 593, 5, "{\"adults\":2,\"children\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@x-cannon.dk\",\"phone\":\"55448877\",\"customer_id\":\"929022\",\"club_id\":\"dw_42589\"},\"addons\":[{\"code\":\"3-1-30136673\",\"price\":200,\"inventory_code\":\"5039\",\"quantity\":1},{\"code\":\"3-1-32709447\",\"price\":300,\"inventory_code\":\"5621\",\"quantity\":2}],\"price\":1350,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\

["12 07 2015", 42618, 833, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@gmail.com\",\"phone\":\"77889944\",\"customer_id\":\"1002728\",\"club_id\":\"dw_42618\"},\"addons\":[{\"code\":\"3-1-38798558\",\"price\":0,\"inventory_code\":\"9867\",\"quantity\":2}],\"price\":2796}"]

["08 11 2015", 42621, 839, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"S\\u00f8s\",\"last_name\":\"x\",\"email\":\"x@gmail.com\",\"phone\":\"11445566\",\"customer_id\":\"833061\",\"club_id\":\"dw_42621\"},\"addons\":[{\"code\":\"3-1-40950192\",\"price\":165,\"inventory_code\":\"E3034\",\"quantity\":2}],\"price\":1628,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

["21 06 2013", null, 1025, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x-x\",\"last_name\":\"x\",\"email\":\"x@hotmail.com\",\"phone\":\"ukendt\",\"customer_id\":\"866978\",\"club_id\":\"dw_42675\"},\"addons\":{\"0\":{\"code\":\"3-1-28304381\",\"price\":175,\"inventory_code\":\"12034\",\"quantity\":2},\"4\":{\"code\":\"3-1-28615023\",\"price\":149,\"inventory_code\":\"c1140\",\"quantity\":1},\"5\":{\"code\":\"3-1-28615024\",\"price\":149,\"inventory_code\":\"c1140\",\"quantity\":1}},\"price\":2144,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

["17 06 2013", 42730, 1197, 5, "{\"adults\":2,\"children\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x.x@telia.com\",\"phone\":\"77441122\",\"customer_id\":\"159296\",\"club_id\":\"sl_24507\"},\"addons\":{\"0\":{\"code\":\"3-1-13379496\",\"price\":195,\"inventory_code\":\"98890\",\"quantity\":2},\"1\":{\"code\":\"3-1-13379497\",\"price\":100,\"inventory_code\":\"98891\",\"quantity\":2},\"2\":{\"code\":\"3-1-13384836\",\"price\":190,\"inventory_code\":\"11101\",\"quantity\":-2},\"6\":{\"code\":\"2-1-34\",\"price\":219,\"inventory_code\":\"31098\",\"quantity\":2},\"7\":{\"code\":\"3-1-13379489\",\"price\":99,\"inventory_code\":\"32223\",\"quantity\":2}},\"price\":8206}"]

["09 11 2016", 42730, 1225, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x.x@telia.com\",\"phone\":\"88995566\",\"customer_id\":\"159296\",\"club_id\":\"sl_24507\"},\"addons\":{\"0\":{\"code\":\"2-1-34\",\"price\":219,\"inventory_code\":\"31098\",\"quantity\":2},\"3\":{\"code\":\"3-1-18899908\",\"price\":0,\"inventory_code\":\"31109\",\"quantity\":2},\"5\":{\"code\":\"2-1-34\",\"price\":219,\"inventory_code\":\"31098\",\"quantity\":2},\"7\":{\"code\":\"2-1-37\",\"price\":495,\"inventory_code\":\"31101\",\"quantity\":2},\"8\":{\"code\":\"3-1-18899910\",\"price\":495,\"inventory_code\":\"87262\",\"quantity\":-1}},\"price\":4467,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

["18 09 2014", null, 1333, 5, "{\"adults\":3,\"children\":1,\"infants\":1,\"guest\":{\"first_name\":\"x\",\"last_name\":\"V\\u00f8lund\",\"email\":\"x@pc.dk\",\"phone\":\"11885566\",\"customer_id\":\"666001\",\"club_id\":\"sl_42914\"},\"price\":1946.5,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/  Anonymiserad ***\"]}"]

 

Dear @Rocco_sprmnt21 

I have provided you with a lot of examples - Will you be able to look at this, hopefully one last time?

Rocco_sprmnt21
Super User
Super User

 

Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(Text.SplitAny(Text.Lower([Column1]),Text.Combine({":".."}"}&{" ".."/"})), Number.From)))

 

View solution in original post

Oopps - Actually not solved. 

 

It only needs to sum the numbers after "Price"

Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(List.Transform(List.Skip(Text.Split([Column1],"price"":")), (t)=> Text.BeforeDelimiter(t,",") ), (tt)=>Number.From(Text.Split(tt,"}"){0}))))

View solution in original post

Dear @Rocco_sprmnt21 

 

It is not calculating, correcly.

 

3016+159 is not 30316.

 

It still need some small tweaking, hope you still can help me? 🙂

 

PBIe.jpg

 

 

you should have specified that there are also decimal numbers.

 

3016.6 is read as 30166 and 30166+150=30316

 

Adesso devi aspettare quando ho tempo di addattare il codice ai nyunmeri decimali (se qualcuno non lo fa prima)

Yes - I am very sorry - I was just going to write to you. 

I didnt knew that was the case before now. 

 

Arh, it did not translate the last part. 🙂


I will wait, thank you so much. You are very kind. 

Table.AddColumn(Origine, "Personalizzato", each List.Sum(List.Transform(List.Transform(List.Skip(Text.Split([Colonna1],"price"":")), (t)=> Text.BeforeDelimiter(t,",") ), (tt)=>Number.From(Text.Split(tt,"}"){0},"en-US"))))

View solution in original post

Dear @Rocco_sprmnt21 

Another twist in this epic saga and I hope you are up for another epic solution.

 

PBIISSUE.png

 

Sometimes after price theres is a Quantity X

So in this case it is 425+425+4968 or

 

(425 * Quantity) + 4968

 

The current formula is this: 

 

Table.AddColumn(Source, "Custom", each List.Sum(List.Transform(List.Transform(List.Skip(Text.Split([Column1],"price\"":")), (t)=> Text.BeforeDelimiter(t,",") ), (tt)=>Number.From(Text.Split(tt,"}"){0},"en-US"))))

 

you should produce a copyable table / list with exhaustive examples of the cases that may occur.

For example, I need to know how  are delimited the numbers following  price and quantity.

On the left there is ":" , but on the right there is always a comma or a } or are there different cases?

Does quantity always follow price?

How many pairs of (price, quantity) can there be?

the last price is without quantity?

etc. etc.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors