Forum Discussion
Extract numbers from string and SUM them
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"))))
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.
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.