Power Query , split multiple columns in one Go

[anupambit1797]

Dear Experts,

                   Greetings!

I have an issue where we have multiple columns to be split with the same delimiter say "=", then why need to select each column every time and perform the operation , is there a way I select all the columns with a common operation and then get the splitted columns?

Thanks & Regards

Anupam Shrivastava

[#2]

@anupambit1797 Perhaps not what you had in mind, but why not merge all columns that you want to split by "=" first and also use "=" as the delimiter for the merge. That would create one column like:

text=text=text=text etc. Then split that column by delimiter "=".

[#3]

Thanks @Riny_van_Eekelen , I attach the raw txt file, and I prepared the report as below using PQ:-

but , here I split each column one by one, can you please help with a similar report with the least number of steps to split all the columns with Delimiter "=" , to get the report like above?

Thanks in Advance,

Br,

Anupam

[#4 · Accepted solution]

@anupambit1797 From earlier posts I conclude that you aren't totally unfamiliar with PQ. Without being mean or rude, I believe you should be able to this yourself. Just do what I suggested above.

Step 1) connect to the txt file

Step 2) select all 10 columns and merge them with "=" as the separator

Step 3) split the resulting single column using "=" as delimiter.

This will turn the original 10 column table into one with 20 columns.

[#5]

Thanks @Riny_van_Eekelen  for this trick, yes it worked 🙂 last step , what step(single or at max 2 steps) shall I use to move the 1st cell of each header as below and simultaneously deleting those rows?

So, that final outcome shall be like this ( below):-

Thanks in Advance,

Br,

Anupam

[#6]

@anupambit1797 The easiest way would be to just rename the relevant columns on-by-one (that's step 1) and then use 'Choose Columns' to select only the columns you renamed (that's step 2).