Forum Discussion
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 2]_..._[text n]_[id]
The problem is, that the [id] part, which I need to extract, may contain the underscore symbol "_". This makes using Split Column useless.
I have a list of these ids (from a different source), that I could probably use in a function like Text.Contains to at least identify which exactly id is in the particular String. But I'm assuming this would involve some sort of iteration (looping) through all the ids in the list. Maybe I overthink myself and there is a more simple solution 🙂 (I did google and search in the form, with no luck)
What's the goal
I need each String ([text1]_[text 2]_..._[text n]_[id]) to be divided into 2 columns:
- [text1]_[text 2]_..._[text n]
- [id]
Any ideas or suggestions are welcome. A solution would amazing.
Thank you!
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"})
5 Replies
I need to see some sample data to work on this. If you could post some sample, that would be great....
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 data Result data Name with id Name Id 30days-b2b-visitors_dynamic_all_4msRKgXG 30days-b2b-visitors_dynamic_all 4msRKgXG 30days-b2b-visitors_dynamic_all_CUSz2R9Z 30days-b2b-visitors_dynamic_all CUSz2R9Z 30days-b2b-visitors_dynamic_all_DG2dv5KS 30days-b2b-visitors_dynamic_all DG2dv5KS 30days-b2b-visitors_dynamic_all_di_a87Xu 30days-b2b-visitors_dynamic_all di_a87Xu 30days-b2b-visitors_dynamic_all_iHNKaES4 30days-b2b-visitors_dynamic_all iHNKaES4 30days-b2b-visitors_dynamic_all_iw4w0y9m 30days-b2b-visitors_dynamic_all iw4w0y9m 30days-b2b-visitors_dynamic_all_jlOadkrD 30days-b2b-visitors_dynamic_all jlOadkrD 30days-b2b-visitors_dynamic_all_mI7sbsrB 30days-b2b-visitors_dynamic_all mI7sbsrB 30days-b2b-visitors_dynamic_all_MiaI2oPB 30days-b2b-visitors_dynamic_all MiaI2oPB 30days-b2b-visitors_dynamic_all_oaxdVgA- 30days-b2b-visitors_dynamic_all oaxdVgA- 30days-b2b-visitors_dynamic_all_pYxvtQil 30days-b2b-visitors_dynamic_all pYxvtQil 30days-b2b-visitors_dynamic_all_qBY3_LcL 30days-b2b-visitors_dynamic_all qBY3_LcL 30days-b2b-visitors_dynamic_all_tYT6m4Av 30days-b2b-visitors_dynamic_all tYT6m4Av 30days-b2b-visitors_dynamic_all_YXEQ30lJ 30days-b2b-visitors_dynamic_all YXEQ30lJ 30days-other-visitors_dynamic_all__yul4_AK 30days-other-visitors_dynamic_all _yul4_AK 30days-other-visitors_dynamic_all_2IAdRAYx 30days-other-visitors_dynamic_all 2IAdRAYx 30days-other-visitors_dynamic_all_3scLSEik 30days-other-visitors_dynamic_all 3scLSEik 30days-other-visitors_dynamic_all_AfRikImf 30days-other-visitors_dynamic_all AfRikImf 30days-other-visitors_dynamic_all_avE_2pSx 30days-other-visitors_dynamic_all avE_2pSx 30days-other-visitors_dynamic_all_-cZjFwRO 30days-other-visitors_dynamic_all -cZjFwRO 30days-other-visitors_dynamic_all_D5EHFa1g 30days-other-visitors_dynamic_all D5EHFa1g 30days-other-visitors_dynamic_all_fYoPYPcB 30days-other-visitors_dynamic_all fYoPYPcB 30days-other-visitors_dynamic_all_O7uWwGlO 30days-other-visitors_dynamic_all O7uWwGlO 30days-other-visitors_dynamic_all_oZUyuovU 30days-other-visitors_dynamic_all oZUyuovU 30days-registered_dynamic_all_ACcSriEU 30days-registered_dynamic_all ACcSriEU 30days-registered_dynamic_all_h2fFvAJA 30days-registered_dynamic_all h2fFvAJA 30days-registered_dynamic_all_mQnRQMsM 30days-registered_dynamic_all mQnRQMsM 30days-registered_dynamic_all_MsZwitSX 30days-registered_dynamic_all MsZwitSX 30days-registered_dynamic_all_OKN0OpCZ 30days-registered_dynamic_all OKN0OpCZ 30days-registered_dynamic_all_SfV92llr 30days-registered_dynamic_all SfV92llr 30days-registered_dynamic_all_u2nFal94 30days-registered_dynamic_all u2nFal94 30days-registered_dynamic_all_UJCjwOMQ 30days-registered_dynamic_all UJCjwOMQ 30days-registered_dynamic_all_yTj51eb5 30days-registered_dynamic_all yTj51eb5 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"})
If this is the pattern, then custom split with delimiter "_all_" (without quotes).
Now, you will get ID in second result column.
In first result column, you can concatenate "_all" again.
To see the working - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZISaws1k0yStItyyzOLMkvKo5PqcxLzM1Mjk/MyYk3yS0O8k6PcFeK1SGsuNAp0jjeJ9lHKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name with ID" = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name with ID", Splitter.SplitTextByDelimiter("_all_", QuoteStyle.Csv), {"Name with ID.1", "Name with ID.2"}), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Name", each [Name with ID.1]&"_all"), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Name with ID.2", "ID"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name with ID.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "ID"}) in #"Reordered Columns"
