Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 required columns using just a single query step, or would I have to do the step for each column individually?

 

Thanks. 

Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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).

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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).

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This worked great, thanks for the help. 🙂

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

     

     

    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

     

     

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    May I ask if your problem has been solved? Is the above post helpful to you?

    The above solutions will all work for you. If  it helps you, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

    In addition, if there are multiple separators in one column, you can also try the Text.SplitAny function

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.