Power Query Replace null values with values from another column

Question

I am working with data imported from a pdf file. There is an extra column in the Power Query import (Data.Column7), containing data that belongs in the adjacent columns on either side (Data.Column6 and Data.Column8). Columns 6 and 8 have null values in the cells where the data was pushed into Column 7. I would like to replace the null values in Columns 6 and 8 with the correct data from Column 7, leaving all other values Columns 6 and 8 as is.

After looking at the post here: Power Query / Power BI - replacing null values with value from another column

and watching this video: https://www.youtube.com/watch?v=ikzeQgdKA0Q

I tried the following formula:

= Table.ReplaceValue(#"Expanded Data",null, each _[Data.Column7] ,Replacer.ReplaceText,{"Data.Column6","Data.Column8"})

(Note, "Expanded Data" is the last step before this Replace Value step.)

I am not getting any kind of syntax error, but the Replace Value step isn't doing anything at all. My null values in Columns 6 and 8 have not been replaced with the correct data from Column 7.

Any insight into how to achieve replacement would be greatly appreciated. Thank you.

(I should mention, I am a new Power Query user, so please be detailed and assume I know nothing!)

Answer 1

If the columns are of the text data type, then it might have empty strings instead of actual nulls.

Try replacing null with "" in your formula.

Answer 2

I'm sure there must be some way to do this with the ReplaceValue function, but I think it might be easier to do the following:

1: Create a new column with definition NewData6= if[Data.Column6]=null then [Data.Column7] else [Data.Column6] 2: Do the same thing for 8 : NewData8= if[Data.Column8]=null then [Data.Column7] else [Data.Column8] 3: Delete Data.Column6/7/8 4: Rename the newly made columns if neccesary.

You can do these steps either in the advanced editor, or just use the create custom column button in the add column tab.