Forum Discussion
Solved
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 p...
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"))))
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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"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\":\"[email protected]\",\"phone\":\"11885566\",\"customer_id\":\"666001\",\"club_id\":\"sl_42914\"},\"price\":1946.5,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"]