Forum Discussion
Create New Column by Extracting Values from Multiple Columns
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 |
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)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.
6 Replies
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 Learnjgeddes 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.
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)