Forum Discussion
Extract Text Before Delimiter for multiple columns in a single query step
Hi there, I have multiple columns in my data set that all require a query step that involves extracting text before delimiter (Text.BeforeDelimiter). Is it possible to invoke this for all the ...
Select all of the columns you want to transform before applying Transform > Extract > Text.BeforeDelimiter and it should apply the step to all of the selected columns in a single step with M code that looks like this:
= Table.TransformColumns(Source, { {"Column1", each Text.BeforeDelimiter(_, "|"), type text}, {"Column2", each Text.BeforeDelimiter(_, "|"), type text}, {"Column3", each Text.BeforeDelimiter(_, "|"), type text} })If you need to, you can edit each of these transformations separately (like if different columns use different delimiters or need an entirely different transformation).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys8sLU/VNTJW0lFKzMmuSUpWyAYykxSMdRRSdBSKi5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t]),
Trfm = List.Accumulate(
{
[ColName="ColA",delim="-"],
[ColName="ColB",delim="|"],
[ColName="ColC",delim=","]
// add as many ColName-delim pairs as you like
},
Source,
(s,c) => Table.TransformColumns(s, {c[ColName], each Text.BeforeDelimiter(_, c[delim])})
)
in
Trfm