TextSplit

[asamr]

Hi,

I have this string where I have succesfully grabed, adults, children and infants.

This has been done by this formula by a fellow community member:

List.Sum(List.Transform(List.Skip(Text.Split([metadata],"adults"":")),
(t)=> try Expression.Evaluate(Text.BeforeDelimiter(t,",") ) otherwise Number.From(Splitter.SplitTextByCharacterTransition({"0".."9"},{",","}"})(t){0},"en-US")))

I have tried tweaking the formula so I can grab market_code (the last 3 lines)

List.Sum(List.Transform(List.Skip(Text.Split([metadata],"market_code"":")),
(t)=> try Expression.Evaluate(Text.BeforeDelimiter(t,",") ) otherwise Number.From(Splitter.SplitTextByCharacterTransition({"0".."9"},{",","}"})(t){0},"en-US")))

The formula is returning an error and I have tried a lot of tweaking.

Anybody able to help me? 

[Vijay_A_Verma · Accepted solution]

You can use following formula for everything here. Just replace "market_code by say adults etc.

= try Text.Split(List.RemoveNulls(List.Transform(Text.Split(Text.Remove([metadata],""""),","),(x)=>if Text.Contains(x,"market_code") then x else null)){0},":"){1} otherwise null

[asamr]

Thank you so much Vijay for your fast response - This is awesome! 🙂