Forum Discussion

asamr's avatar
asamr
Icon for Helper I rankHelper I
5 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

     

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

     

  • Anonymous's avatar
    Anonymous
    5 years ago
    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}))))
  • Anonymous's avatar
    Anonymous
    5 years ago
    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"))))
asamr's avatar
asamr
Icon for Helper I rankHelper I
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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

 

  • asamr's avatar
    asamr
    Icon for Helper I rankHelper I
    5 years ago

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