Forum Discussion

MrLidums's avatar
MrLidums
Frequent Visitor
4 years ago
Solved

Extract id from the string

Hi.   Problem I need to split the String into separate parts. Usually, I would use the built-in Split Column function. However, in this case, I have data (String) with this pattern: [text1]_[text...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    If the [id] can context letters and underscores, then it could theoretically contain "_all_", so splitting on that might not be the best approach, especially if not all of the names end with that.

     

    It looks like your [id] always has 8 characters, so why not split on that?

    Table.SplitColumn(Source, "Name with id", Splitter.SplitTextByPositions({0, 8}, true), {"Name", "Id"})

     

MrLidums's avatar
MrLidums
Frequent Visitor
4 years ago

Hi Vijay_A_Verma ! 

thank you for the quick response. 

Sure, I can post some samples. Here you go. Let me know if you prefer them in any other form 🙂 

I made in bold ids that are messing up the auto spliter function.

 

Original dataResult data 
Name with idNameId
30days-b2b-visitors_dynamic_all_4msRKgXG30days-b2b-visitors_dynamic_all4msRKgXG
30days-b2b-visitors_dynamic_all_CUSz2R9Z30days-b2b-visitors_dynamic_allCUSz2R9Z
30days-b2b-visitors_dynamic_all_DG2dv5KS30days-b2b-visitors_dynamic_allDG2dv5KS
30days-b2b-visitors_dynamic_all_di_a87Xu30days-b2b-visitors_dynamic_alldi_a87Xu
30days-b2b-visitors_dynamic_all_iHNKaES430days-b2b-visitors_dynamic_alliHNKaES4
30days-b2b-visitors_dynamic_all_iw4w0y9m30days-b2b-visitors_dynamic_alliw4w0y9m
30days-b2b-visitors_dynamic_all_jlOadkrD30days-b2b-visitors_dynamic_alljlOadkrD
30days-b2b-visitors_dynamic_all_mI7sbsrB30days-b2b-visitors_dynamic_allmI7sbsrB
30days-b2b-visitors_dynamic_all_MiaI2oPB30days-b2b-visitors_dynamic_allMiaI2oPB
30days-b2b-visitors_dynamic_all_oaxdVgA-30days-b2b-visitors_dynamic_alloaxdVgA-
30days-b2b-visitors_dynamic_all_pYxvtQil30days-b2b-visitors_dynamic_allpYxvtQil
30days-b2b-visitors_dynamic_all_qBY3_LcL30days-b2b-visitors_dynamic_allqBY3_LcL
30days-b2b-visitors_dynamic_all_tYT6m4Av30days-b2b-visitors_dynamic_alltYT6m4Av
30days-b2b-visitors_dynamic_all_YXEQ30lJ30days-b2b-visitors_dynamic_allYXEQ30lJ
30days-other-visitors_dynamic_all__yul4_AK30days-other-visitors_dynamic_all_yul4_AK
30days-other-visitors_dynamic_all_2IAdRAYx30days-other-visitors_dynamic_all2IAdRAYx
30days-other-visitors_dynamic_all_3scLSEik30days-other-visitors_dynamic_all3scLSEik
30days-other-visitors_dynamic_all_AfRikImf30days-other-visitors_dynamic_allAfRikImf
30days-other-visitors_dynamic_all_avE_2pSx30days-other-visitors_dynamic_allavE_2pSx
30days-other-visitors_dynamic_all_-cZjFwRO30days-other-visitors_dynamic_all-cZjFwRO
30days-other-visitors_dynamic_all_D5EHFa1g30days-other-visitors_dynamic_allD5EHFa1g
30days-other-visitors_dynamic_all_fYoPYPcB30days-other-visitors_dynamic_allfYoPYPcB
30days-other-visitors_dynamic_all_O7uWwGlO30days-other-visitors_dynamic_allO7uWwGlO
30days-other-visitors_dynamic_all_oZUyuovU30days-other-visitors_dynamic_alloZUyuovU
30days-registered_dynamic_all_ACcSriEU30days-registered_dynamic_allACcSriEU
30days-registered_dynamic_all_h2fFvAJA30days-registered_dynamic_allh2fFvAJA
30days-registered_dynamic_all_mQnRQMsM30days-registered_dynamic_allmQnRQMsM
30days-registered_dynamic_all_MsZwitSX30days-registered_dynamic_allMsZwitSX
30days-registered_dynamic_all_OKN0OpCZ30days-registered_dynamic_allOKN0OpCZ
30days-registered_dynamic_all_SfV92llr30days-registered_dynamic_allSfV92llr
30days-registered_dynamic_all_u2nFal9430days-registered_dynamic_allu2nFal94
30days-registered_dynamic_all_UJCjwOMQ30days-registered_dynamic_allUJCjwOMQ
30days-registered_dynamic_all_yTj51eb530days-registered_dynamic_allyTj51eb5
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

If the [id] can context letters and underscores, then it could theoretically contain "_all_", so splitting on that might not be the best approach, especially if not all of the names end with that.

 

It looks like your [id] always has 8 characters, so why not split on that?

Table.SplitColumn(Source, "Name with id", Splitter.SplitTextByPositions({0, 8}, true), {"Name", "Id"})

 

  • MrLidums's avatar
    MrLidums
    Frequent Visitor
    4 years ago

    AlexisOlson 

    your observation is amazing! I checked all are 8 char long. I even checked with our devs and they confirmed that it is a randomly generated 8 char long text identifier.

     

    How I love simple solutions, thank you very much 👍. 

    Also, next time, when dealing with any kind of id type of data will check the length firsthand 😄