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...
Need Help
  • 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

     

     

     

serpiva64's avatar
serpiva64
Icon for Solution Sage rankSolution Sage
3 years ago

Hi,

i extracted text after delimiter

then added a custom column

then extracted first characters from Column1

and finally merged in the right order

 

 

 

jadempsey's avatar
jadempsey
New Member
3 years ago

This is great and we are almost there.  The 0831 code could also be 0833 or 0835 or 0837.  Can we do the same thing by using 083 as the delimiter and then adjusting the count code somehow?

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    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