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