Forum Discussion
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...
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"