Create New Column by Extracting Values from Multiple Columns

[Anonymous]

The goal is to extract 5 digit value from multiple columns and create a new column with the extracted values. For more context, below is sample data. I am familiar with accomplishing this task after loading data into PBIX and using SWITCH function to accomplish this. However this scenario requires me to accomplish this task while in Power Query prior to data load. Any advice will be greatly appreciated. 

Sample Data:

Entity	Assignee
Steph Curry	MGR12345
Michael Jordan	MGR12346
Lebron James	MGR12347
Kobe Bryant	MGR12348
12349_Ops	9988073

Expected Results: Notice new column "Store_Number". The new column comprises of extracted values from both Entity + Assignee.

Entity	Assignee	Store_Number
Steph Curry	MGR12345	12345
Michael Jordan	MGR12346	12346
Lebron James	MGR12347	12347
Kobe Bryant	MGR12348	12348
12349_Ops	9988073	12349

[jgeddes · Accepted solution]

No worries. 
Here is the code that will look for MGR first...

= Table.AddColumn(#"Changed Type", "Store", each if Text.Contains([Assignee], "MGR") then Text.AfterDelimiter([Assignee], "MGR") else if Text.PositionOfAny([Entity], {"0".."9"}) >= 0 then Text.Select([Entity], {"0".."9"}) else null, type text)

[jgeddes]

There is not enough sample data provided to give a definitive solution but as starting point the following code will check the Entity column if it contains numbers and extracts them if they exist. If the Entity column does not contain numbers then numbers are extracted from the Assignee column.

= Table.AddColumn(#"Changed Type", "Custom", each if Text.PositionOfAny([Entity], {"0".."9"}) >= 0 then Text.Select([Entity], {"0".."9"}) else Text.Select([Assignee], {"0".."9"}), type number)

I would suggest you read up on the Text Functions of M to help you with your actual solution.
Text Functions in M - Microsoft Learn 

[Anonymous]

@jgeddes Thank you so much for your support. Lets look at it in a different angle. How would you frame this if you were to look at the ASSIGNEE column first. If "MGR" is present in the ASSIGNEE column, then extract the 5 digit values. If "MGR" is not present, are there numbers in the ENTITY column. Is there a way to update your sample code that would look at it in this angle?

Apologies if this seems very amature, M is very foreign to me. Your support is greatly appreciated. 

[jgeddes · Accepted solution]

No worries. 
Here is the code that will look for MGR first...

= Table.AddColumn(#"Changed Type", "Store", each if Text.Contains([Assignee], "MGR") then Text.AfterDelimiter([Assignee], "MGR") else if Text.PositionOfAny([Entity], {"0".."9"}) >= 0 then Text.Select([Entity], {"0".."9"}) else null, type text)

[Anonymous]

@jgeddes Thank you again for your support. Question for you...if the 5 digit value has a 0 in front of it (ex: 02495) and I want to get the 4 digit value (ex: 2495), how can I achieve this? Or even 3 digit (ex: 00912 >> expected output: 912). I am aware that this can be done if the type is switched to number but I have to keep the type as text. A slight tweak to what you provided and it is almost complete. Any thoughts? 

= Table.AddColumn(#"Changed Type", "Store_Number", each if Text.Contains([Assignee], "MGR") then Text.AfterDelimiter([Assignee], "MGR") else if Text.PositionOfAny([Entity], {"0".."9"}) >= 0 then Text.Middle([Entity], 0, 5) else null, type text)

[jgeddes · Accepted solution]

I would use Text.Trim in a subsequent step in the query. 

If you select the Store_Number column and select Transform->Format->Trim in the ribbon you will end up with code that looks like...

= Table.TransformColumns(Custom1, {{"Store_Number", Text.Trim, type text}})

change that code to...

= Table.TransformColumns(Custom1, {{"Store_Number", each Text.TrimStart(_, "0"), type text}})

and it will trim any "0" from the start of the string.

[Anonymous]

@jgeddes Outstanding! Thank you!