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.
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.
jgeddes Outstanding! Thank you!