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 ...
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.
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!