|
|
Thread Tools | Display Modes |
#1
|
||||
|
||||
Remove extra delimiters in power query from merged columns with null values
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. |
#2
|
||||
|
||||
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"}) There are probably better/more elegant ways! |
#3
|
||||
|
||||
eg. with:
Code:
Result = Table.AddColumn(PreviousStep, "Custom", each Text.Combine(List.Select({[Hdr1],[Hdr2],[Hdr3],[Hdr4],[Hdr5],[Hdr6]}, each _<> "" and _ <> null),";")) |
#4
|
||||
|
||||
The error message says: could not find Table.CombineColumnsToRecord.....
|
#5
|
||||
|
||||
Try the one in the next message then...
|
#6
|
||||
|
||||
Token comma error in the second expression.
|
#7
|
||||
|
||||
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 |