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