PowerQuery Extract Code with specific Pattern

[volpinara]

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]

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 · Accepted solution]

Hi @volpinara,

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

Regards,

Frank

[volpinara]

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