I have a column that includes text consisting of 7 consecutive non-blank characters in the format AAANNNN (Alpha A-Z and Numeric 0-9).
I want to extract this field the 7 characters and place the extracted text string in a new Column. If the AAANNNN pattern does not exist on a rowthen no output is made.
The current M code does not do this. I have also tried an approach using by "Add Columns By Example".
Does anyone have any ideas?
Thank You
I want to extract this field the 7 characters and place the extracted text string in a new Column. If the AAANNNN pattern does not exist on a rowthen no output is made.
The current M code does not do this. I have also tried an approach using by "Add Columns By Example".
Does anyone have any ideas?
Thank You
By Example (revised.xlsx | |||||
---|---|---|---|---|---|
F | G | H | |||
2 | ID | ||||
3 | (ABC:FGH4567) | ||||
4 | (ABC:HJK4567 | ||||
5 | ABC:GH3456 | ||||
6 | ABC:A KL5678 | ||||
7 | :YUI4567 | ||||
8 | ABC:HJKO1234 | ||||
9 | GHK1234 | ||||
10 | :GHY5672)) | ||||
11 | :GHY567)) | ||||
12 | |||||
13 | |||||
14 | |||||
15 | |||||
16 | ID | Custom.1 | Comment | ||
17 | (ABC:FGH4567) | should be FGH4567 | |||
18 | (ABC:HJK4567 | HJK4567 | OK | ||
19 | ABC:GH3456 | Correct treatment as not AAA1234 format | |||
20 | ABC:A KL5678 | Correct Output as it contains a blank | |||
21 | :YUI4567 | YUI4567 | OK | ||
22 | ABC:HJKO1234 | Correct treatment as not AAA1234 format | |||
23 | GHK1234 | GHK1234 | OK | ||
24 | :GHY5672)) | should be GHY5672 | |||
25 | :GHY567)) | Correct treatment as not AAA1234 format | |||
Sheet1 |
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.Length(List.Last(Text.Split([ID], ":")))=7 then List.Last(Text.Split([ID], ":")) else null)
in
#"Added Custom"