Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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}
Solved! Go to Solution.
Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(Text.SplitAny(Text.Lower([Column1]),Text.Combine({":".."}"}&{" ".."/"})), Number.From)))
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}))))
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"))))
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:
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 @Anonymous
I have provided you with a lot of examples - Will you be able to look at this, hopefully one last time?
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"
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? 🙂
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"))))
Dear @Anonymous
Another twist in this epic saga and I hope you are up for another epic solution.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.