Forum Discussion
Helper IExtract 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"))))
Helper IDear 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)
- asamr
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"))))- asamr
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"))))