Extract id from the string

[MrLidums]

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!

[Vijay_A_Verma]

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"

[Vijay_A_Verma]

I need to see some sample data to work on this. If you could post some sample, that would be great....

[MrLidums]

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

[AlexisOlson · Accepted solution]

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]

@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 😄