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