Fill blank cells with values from a cell beside it (not merge)

Copper Contributor

Hi everyone,

 

Is there an easy way that excel can help fill in blanks efficiently if there are multiple columns that might have similar data and you want bring multiple columns together into one.

 

Please see the example below.

 

Product IDID of Product
43242424324242
324234264564
 453
 43543
 435
432424 
 345
 5453453
4234234 
 53453543
 5345435
423432423 
 534543
 453453
423 
 45435
42534534

 

As you can see, there's 2 columns that I want to merge into one, but some of them have information in BOTH columns, in that case I'd want to prioritize one column over another (not concatenate or merge them with a separator.

The Master column, let's call it, should have only 1 product ID.

 

How could I do this, and if that is possible, how can I do it with more than 2 columns.

Also, can I do this within power query editor?

 

Thanks so much

1 Reply

@Nick_L1025 

My knowledge of PQ is limited (to put it mildly :), but I was able to find this solution proposal on the internet.

Power Query Editor by using the Table.ReplaceValue function.

For example, you can use this code:

 

= Table.ReplaceValue(#“Changed Type”,null,[ID of Product],Replacer.ReplaceValue,{“Product ID”})

 

This will replace any null values in the Product ID column with the values from the ID of Product column.

 

I hope this helps.