Merge columns based on condition

[Dom87326]

Hello Experts!

A have a large number of columns per table that end with 'Other'. How can I merge these columns (1 to 2) if the condition (column name to end with 'Other') gets satisfied in power query and new merged column name should be the same as before (i.e. 1)?

The end result ideally should display column 1 name, column 2 value and delete column 2 for all table.

Many thanks in advance!

[Anonymous]

Hi @Dom87326 ,

Could you tell me if @Vijay_A_Verma 's method helps you solve the issue?If it does, kindly Accept it as the solution.

More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Vijay_A_Verma · Accepted solution]

Use this. You will need to replace #"Previous Step" and Column1 as per you need.

Then you can right click on 2nd column and Remove column

= Table.ReplaceValue(#"Previous Step",each [Column1],each if [Column1]<>null and [Other]<>null then [Column1]&" "&[Other] else [Column1],Replacer.ReplaceValue,{"Column1"})

[Dom87326]

Hey @Vijay_A_Verma , 

Ideally I'd like to see this merged column with 1st column name from the above: 

Thanks!

[Vijay_A_Verma]

Can you post the result for the screenshot posted?