Search code examples
excelextractpowerquerym

Power Query Extract Column Text while retaining original column contents?

How can I extract text from a column in powerQuery without altering the contents of the original column? (i.e. copy a partial string and then paste into a new column)

I'm trying to clean up my applied steps for data wrangling and currently I have to duplicate a column, extract text from the duplicated column, and then rename the column. I have multiple columns to do this to and it creates a lot of steps.

Is there anyway to extract partial string from a column -- but retain the original column? (i.e. extract to a new column).

Code below:

Duplicated_Column = Table.DuplicateColumn(Combine_FilePath_and_Name, "File & Path", "File & Path - Copy"),
Renamed_Columns = Table.RenameColumns(Duplicated_Column,{{"File & Path - Copy", "V Program"}}),
Extract_Vehicle_Program = Table.TransformColumns(Renamed_Columns, {{"V Program", each Text.BetweenDelimiters(_, "\B3\", "\"), type text}}),
Solution

  • You can extract and add a new column at the same time. Similar to below

    #"Added Custom" = Table.AddColumn(Combine_FilePath_and_Name, "V Program", each Text.BetweenDelimiters([#"File & Path"], "\B3\", "\"))

    If you have to do this with a large group of columns, look into unpivoting the data then doing the transform a single time