Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

 

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

 

View solution in original post

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

15 REPLIES 15
Anonymous
Not applicable

 

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?

Anonymous
Not applicable

"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 ***\"]}"]

 

Anonymous
Not applicable

 

Dear @Anonymous 

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

Anonymous
Not applicable

 

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

 

Oopps - Actually not solved. 

 

It only needs to sum the numbers after "Price"

Anonymous
Not applicable

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}))))

Dear @Anonymous 

 

It is not calculating, correcly.

 

3016+159 is not 30316.

 

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

 

PBIe.jpg

 

 

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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"))))

Dear @Anonymous 

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"))))

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors