Remove extra delimiters in power query from merged columns with null values

Marcia · #1 02-16-2021, 12:13 AM

Hi. I have merged five columns by power query in both add column and transform tab. Some of the cells have null values and the result was like:

Abc;;klm;;
Any idea on how to remove the extra delimeters in the PQ?
Thank you.

p45cal · #2 02-16-2021, 09:02 AM

try:

Code:

Result = Table.RemoveColumns(Table.AddColumn(Table.CombineColumnsToRecord(PreviousStep,"merged",{"Hdr1","Hdr2","Hdr3","Hdr4","Hdr5","Hdr6"}), "Custom", each Text.Combine(List.RemoveNulls(Record.ToList([merged])),";")),{"merged"})

where {"Hdr1","Hdr2","Hdr3","Hdr4","Hdr5","Hdr6"} is the list of column headers you want to merge and Custom is the name of the resultant column. The header merged can be anything as long as it is the same throughout; it really doesn't matter what it's called since it's created then deleted in the same line (of course it mustn't be the same as another pre-existing column name).

There are probably better/more elegant ways!

p45cal · #3 02-16-2021, 09:48 AM

Quote:

Originally Posted by p45cal

There are probably better/more elegant ways!

eg. with:

Code:

Result = Table.AddColumn(PreviousStep, "Custom", each Text.Combine(List.Select({[Hdr1],[Hdr2],[Hdr3],[Hdr4],[Hdr5],[Hdr6]}, each _<> "" and _ <> null),";"))

Marcia · #4 02-18-2021, 09:33 PM

The error message says: could not find Table.CombineColumnsToRecord.....

p45cal · #5 02-18-2021, 10:52 PM

Try the one in the next message then...

Marcia · #6 02-18-2021, 11:11 PM

Quote:

Originally Posted by p45cal

Try the one in the next message then...

Token comma error in the second expression.

p45cal · #7 02-19-2021, 02:18 AM

Supply a little test file.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Remove delimiters in merged columns in power query when null	Marcia	Excel	2	11-06-2020 09:42 PM
Power Query to combine multiple tables of unequal number of columns	ProudLiberal	Excel	1	01-07-2020 07:54 PM
How to unmerge all merged cell and fill them with the merged values?	Bumba	Excel Programming	1	11-10-2019 11:36 AM
Query and Power Query not working Excel 2016	bl10	Excel	0	07-22-2016 06:25 PM
Display Null Values in Pivot as 0	lornalou	Excel	2	06-02-2016 10:39 AM