#PowerQuery – Replace Values in one column with the value in another column

Just wanted to share a method in Power Query that might also help you. Today I had a table where the column contained information about both the parent element and child element

In the example the CategoryInfo column contains both the Category and sub Category information.

And I wanted it to be transformed into this

Let’s first add a conditional column that checks whether the row contains a Category and if so insert the text from the CategoryInfo Column

Next up is the Replace Values magic.

The Replace Values dialog doesn’t support referencing a column

In the example I just add a step where I search for C and replace it with “”.

This step is not good enough so I modify the function parameter to this

= Table.ReplaceValue(#”Changed Type”, each [SubCategory], null, Replacer.ReplaceValue, {“CategoryInfo”})

The second argument uses

each [SubCategory]

to use the value in SubCategory in each row at the “Value to Find”

The third argument uses

null

as the Replace With

The fourth argument is changed to

Replacer.ReplaceValue

Instead of Replacer.ReplaceText so it will replace the whole value of the column to search in and replace it with null

This will give us

And now we can use fill Down to insert the CategoryInfo in all the null cells

And finally filter all rows where SubCategory is null to end up the final result

You can download the example file here – link

Power Query On !!!

4 thoughts on “#PowerQuery – Replace Values in one column with the value in another column

  1. Hi Erik,
    From one Dane to another. This is actually the best practical solution I found on Google
    It is quite complex to understand – But when you get your head around the solution it is easy to follow and the example was perfect.
    regards
    Jørgen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s