Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marcofalzone
Helper I
Helper I

AddColumn and Transform in same PowerQuery statement

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:

Screenshot_2.jpg

 

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"00), type text}})
 
Example:
MyStartDelimiter = "/"
MyEndDelimiter = "\"

MyColumn      MyNewColumn
SomeTextBeforeDelimiter/MyString1\SomeTextAfterDelimiter MyString1
SomeTextWithNoDelimiter (blank)
SomeTextBeforeDelimiter/MyString2\SomeTextAfterDelimiter MyString2
 
Thanks everybody for your help!
Marco
1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

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)

 
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

1 REPLY 1
AllisonKennedy
Super User
Super User

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)

 
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.