Forum Discussion

asamr's avatar
asamr
Helper I
4 years ago
Solved

TextSplit

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? 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
      asamr
      Helper I
      4 years ago

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