topic TextSplit in Power Query

TextSplit

asamr — Thu, 04 Aug 2022 13:21:08 GMT

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?

Re: TextSplit

Vijay_A_Verma — Thu, 04 Aug 2022 13:51:33 GMT

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

Re: TextSplit

asamr — Fri, 05 Aug 2022 04:21:31 GMT

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