Forum Discussion

volpinara's avatar
volpinara
Frequent Visitor
7 years ago
Solved

PowerQuery Extract Code with specific Pattern

Hello everyone,

 

I'm going crazy trying to extract a substring, matching particular pattern : 00-0000. My idea is to use PowerQuery.

 

I've a long list of text like:

 

[Name]:

bar-cacciatopo-pluto

Pippo-PlutoCMD 17-1012 - asd

45-236 CMD 18-1011

topotopo 15-1500pippo -mao

 

My purpose is to extract all the 18-1000, if present

a) determinate the position of all the "-" : using List.PositionOfAny([ListColumn], Occurrence.All) ?

b) check if

     -   in the two characters before are numbers AND

     -   in the next 4 characters are numbers 

c) Extract these 2 characters & "-" & 4characters

 

At the moment I have no idea how to loop among all found "-" and perform the desired checking... 

Do you have any suggestion?

 

Many thanks!

 

 

 

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi volpinara,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.

     

    Regards,

    Frank

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi volpinara,

     

    You can following the steps to meet your requirement.

     

    1. Replace all "-" to 0 in power query.

     

    2. Add a custom column using the formula.

     

    List.Select(Text.SplitAny([Column1],Text.Remove([Column1],{"0".."9"})),
(x)=>Text.Length(x)=7){0}

    3. Split the new column.

     

    Here is the M code in advanced editor for your reference.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkos0k1OTE7OTCzJL8jXLcgpLclXitWJVgrILADyA0B8Z18XBUNzXUMDQyMFXYXE4hSwAhNTXSNjMwWwpAVI0hAsDDIGhBUMTXUNTQ0MCkDmKOjmJgKNjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","-","0",Replacer.ReplaceText,{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.Select(Text.SplitAny([Column1],Text.Remove([Column1],{"0".."9"})),
(x)=>Text.Length(x)=7){0}),
    #"Split Column by Position" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByPositions({0, 2}, false), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Custom.2", Splitter.SplitTextByPositions({0, 1}, false), {"Custom.2.1", "Custom.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Custom.2.1", type text}, {"Custom.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Custom.2.1"})
in
    #"Removed Columns"

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

     

     

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi volpinara,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.

       

      Regards,

      Frank

      • volpinara's avatar
        volpinara
        Frequent Visitor
        7 years ago

        Hi v-frfei-msft,
        my apologize for the long delay! I was on holidays with poor connection.

        Many thanks for your suggestion, it works PERFECTLY!!

        Best Regards