PowerQuery Extract Code with specific Pattern

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