Forum Discussion

asamr's avatar
asamr
Helper 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 price and sum then.

 

Any idea how to solve this?

 

 

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

 

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

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

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

     

    • asamr's avatar
      asamr
      Helper I
      5 years ago

      Oopps - Actually not solved. 

       

      It only needs to sum the numbers after "Price"

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

     

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

     

    • asamr's avatar
      asamr
      Helper 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"