Find letter and get the letter and 4 numbers to the right

[Bergh]

Hey all. I have a problem in Power Query. I have a column that contains different addresses. It might look like this: 682P461601D8126, 682P471402D8129.
What you want is to find "P" and take "P" and the four numbers to the right of "P", like this P4616, P4714.

Then it should be the same with "D", D8126, D8129. "P" should be in one column "D" should be in one column and so on. I have "C","L" & "S" included.
I have tested with this code but I only get circular problems,

let
// Assuming your table is named "NightPickLine"
Source = YourTableName,

// Add a custom column to find the position of the first "P" and extract the substring
AddCustomColumn = Table.AddColumn(Source, "Result", each
let
// Your input text is taken from the "PickAdress" column
inputText = [PickAdress],

// Find the position of the first "P"
positionOfL = Text.PositionOf(inputText, "P"),

// Check if "P" is found
result = if positionOfL = -1 then
"P not found"
else
// Extract "P" and the next four characters
Text.Middle(inputText, positionOfL, 5)
in
result
)
in
AddCustomColumn

[watkinnc]

That is a good solution, but I encourage you to think about techniques like replacing your "P"s and "D"s with something like "-P" and "-D". Then you can simplify the process by using the SplitByDelimiter functions, using the "-", thus adding your new columns with simple splits, without having to figure anything out at alll about positions and circular stuff. Then you just keep the left 5 digits. This way you are not having to find positions of text.

Replace, Split, get the Left 5, done.

--Nate

[AlienSx · Accepted solution]

Hi, @Bergh this code works just fine. Looks like the person trying to incorporate this code uses Table.AddColumn (of course, he/she wants to get a new column). Create new blank query, copy and paste this code in Advanced Editor and you should be good. 

[Bergh]

Hi @AlienSx Thanksfor the input it worked, but how do I write the code if I want all the different letters and numbers to appear in the new query?

[AlienSx]

@Bergh would you like to keep each letter & 4 digits combo in separate columns? Like "P" for P1234, "D" for D1243 etc.? 

[Bergh]

I think I fixed it.

let
// Assuming your table is named "YourTableName"
Source = YourTableName,

// List of letters to extract
lettersToExtract = {"L", "C", "S", "D", "P", "W"},

// Function to add custom columns for each letter
AddCustomColumns = List.Accumulate(
lettersToExtract,
Source,
(table, letter) =>
let
// Add a custom column to find the position of the current letter and extract the substring
customColumnName = "Result_" & letter,
AddCustomColumn = Table.AddColumn(table, customColumnName, each
let
// Your input text is taken from the "PickAdress" column
inputText = [PickAdress],

// Find all positions of the current letter in the input text
positions = Text.PositionOfAny(inputText, {letter}, Occurrence.All),

// Extract the letter and the next four characters for each position
results = List.Transform(positions, each
let
position = _,
result = if position = -1 then
letter & " not found"
else
Text.Middle(inputText, position, 5)
in
result
)
in
results
)
in
AddCustomColumn
)
in
AddCustomColumns