Forum Discussion

jadempsey's avatar
jadempsey
New Member
3 years ago
Solved

Power Query extract function based upon specific text and returning specific number of characters

I am transforming a data set and extracting a series of numbers which need to be in the final format of ####-####. Every row will contain this format of numbers and each number will start with 083 pl...
  • serpiva64's avatar
    serpiva64
    3 years ago

    Hi,

    you can do almost the same

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDLDcMwDEN3ybkBRFE/zxJk/zWa1q2LKkc/PNK0j2MTpOxSxK4B1e18XOx1zMhUygTI0ikRsRAmUs9uWcrNkmmt63wYvpK8kTFCG4KNjsLZkQxryGWw1xvIheZSR96CKj1Y7COc0uvd5NaVtR5EfKxR+P1yqBf/QyH0Xu2GVh06+kwvd3YUuKzzCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, "083"), type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Text After Delimiter", "Custom", each "083"),
    #"Extracted First Characters" = Table.TransformColumns(#"Added Custom", {{"Column1", each Text.Start(_, 6), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"Custom", "Column1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
    in
    #"Merged Columns"

     

    i extracted text after delimiter 083

    then added a custom column 083

     

     

    then extracted 6 first characters from Column1 

     

    and finally merged in the right order