AddColumn and Transform in same PowerQuery statement

[marcofalzone]

Hello everybody,

anyone knows some way to merge 2 Power Query statements into one?

The following queries do a double step:

• Step 1: creates a new column based on another existing column (field type = text)
• Step 2: extract a string in the new column, based on delimiters

I would like to merge the code into one step, to improve efficiency on transformation.

My Actual Code:

My Code Structure:

  #"Duplicates MyColumn" = Table.AddColumn(#"Previous Step Reference", "MyNewColumn", each [MyColumn], type text),

  #"Extracts String" = Table.TransformColumns(#"Duplicates MyColumn", {{"MyNewColumn", each Text.BetweenDelimiters(_, "MyStartDelimiter", "MyEndDelimiter", 0, 0), type text}})

Example:

MyStartDelimiter = "/"

MyEndDelimiter = "\"

MyColumn		MyNewColumn
SomeTextBeforeDelimiter/MyString1\SomeTextAfterDelimiter		MyString1
SomeTextWithNoDelimiter		(blank)
SomeTextBeforeDelimiter/MyString2\SomeTextAfterDelimiter		MyString2

Thanks everybody for your help!

Marco

[AllisonKennedy · Accepted solution]

Having these as two steps isn't necesarily a bad thing for performance, but you can use the 'Add column' tab to select the Extract button from your [Refer] or [OriginalColumnName] column. The code should look similar to this: 

= Table.AddColumn(#"Previous step name", "Text Between Delimiters", each Text.BetweenDelimiters(Text.From([OriginalColumnName], "en-US"), "mystart", "myend"), type text)