extract pattern of 4 letters and 6 numbers in file names

[jsbourni]

Hi,

Been struggling (even with ChatGPT) to find this one. I extracted all the file names of specific folders in Excel using Power Query. File names are essentially IDs and are supposed to identify individuals using this shape: AAAA123456-12345678. I realized that technicians renamed some of the files to attach a status to it like this: 014 - AAAA123456-12345678.

I would like to create a new column that would only contain this: AAAA123456

Patterns are stable in length but obviously letters and numbers vary a lot.

ChatGPT suggested many "functions" that aren't even supported by Power Query. I tried with regex functions and nothing seems to work.

= Table.AddColumn(#"Previous Step", "ExtractedPattern", each Text.Select([Column Name], "[A-Za-z]{4}[0-9]{6}"))
= Table.AddColumn(#"Previous Step", "ExtractedPattern", each Text.Select([Column Name], {"A".."Z", "a".."z", "0".."9"}))

I tried to eliminate non-text characters like = Table.AddColumn(#"Previous Step", "ExtractedPattern", each Text.Select([Column Name], "([^A-Za-z0-9]*[A-Za-z]{4}[0-9]{6})"))

Thanks for your help

J

[Anonymous]

You can just use the GUI "Extract" menu and choose Text Before Delimiter, split once from the end using "-". Then extract Text After Delimiter, using "-" as the delimiter. 

--Nate

[ChielFaber · Accepted solution]

You could try to make a column that formats both values are the same. So the same buildup for values with an attached status and without. You could do this by counting the number of - characters in the field. 

You can count the number of - characters with the following solution:

https://community.powerbi.com/t5/Desktop/How-to-count-a-specify-letter-in-a-string-in-power-query-m/...

Create a custom column in which you count the number of times the - character occurs in a field.

List.Count(Text.PositionOf([columname],"-",Occurrence.All ))

According to your example values with a status will have 2 - characters and without an attached status will have 1.

You can then use this column to create equal values

if numberofcharacters = 1 then Text.Combine("1-", Filenamecolumn) else Filenamecolumn. The value with the status will now look the same (014 - AAAA123456-12345678), but the value without a status should now look like

1-AAAA123456-12345678. This will make it possible to split the values using the delimiter -. Both will now split the same way. The second split will give you the result your looking for.

[jsbourni]

Wow, I was not looking in the right direction. Intead of trying to compose with irregular structures, I should standardize and then extract. Really great advice. Thanks.