Power Query Replace Values the safer way

2 lines of code can save you from having to refer directly to columns and therefore make you refresh more robust in the future.

Whether it's replacing nulls with zeros or any other replacement this technique can help

Content

00:00 Intro
00:19 The Scenario
01:00 How we do it
03:25 Table.ColumnNames
03:40 Editing Table.ReplaceValues
04:51 Subscribe

Follow me on LinkedIn

Twitter

Access Analytic Blog

[garylhaas]

I skill don't "grasp" the full power of Lists - but each one of your videos brings me closer

[rajeshmajumdar]

Thanks a ton!!! I had a situation where I needed to split a column by delimiter ... now I don't know how many columns I may have for numbers ... and all blank columns have nulls ... I just referenced the base query and used your formula... It worked. God bless you.

[brunof.s.]

Casually watching YouTube tutorials and stumbled to yours. Check my data, check your video.... BANG! Just put it into the data set I was working with. Super thanks!

[GrainneDuggan_Excel]

So simple and so very helpful.

I am glad to see the intellisense annoyances pointed out. Hopefully they get adjusted soon

[biljanamajstorova]

Power query is so unlimited with options, and there is no right or wrong way to clean the data. For this particular goal i would unpivot other columns, apply the replace null to zero, and than pivot again. We'll receive the same result🙂 Usualy when cleaning data i apply more than one changes to the values from the source data, like change data type for value columns, divide with 1000 to receive k values, or convert to Fx, rounding, merge with lookup table and so on...than pivot again if output is needed in columns. Like the opt you show since it is a step forward to understand better the M language and utilize when there is no "button" to apply complex transformations 🙂

[Bhavik_Khatri]

Bloody marvelous! I'm learning PQ and I was thinking of FirstN function, but your approach is very good. I learnt something new today. Thank you very much.

[antoniogarrido]

Brilliant.! Really useful and applicable solution for a usual problem..., thanks!!

[joescanlonh]

Nice, exactly what I have been dealing with hitting refresh and broken headers. Thank you.

[VikingGuard]

Love this little trick. Handy in when one is sourcing Rest api from all sorst of webservices.

[shadrackawunyo]

Beautifully done sir. Very applicable in a variety of scenarios

[DancingZeliha]

That was awesome. I am glad I watched it even though it wasn't quite addressing the issue I was researching. I was certainly rewarded with some very helpful and useful tips. Thank you.

[khalidmijhad]

So many hours of work I would have saved if I new this before

[doviedk]

That was awesome! Nice explanation of the steps required.

[Psyresidente]

Great video. How can I replace multiple different strings from a column in one step ? I'm trying to find this solution but is nowhere.

[defaultHandle]

Okay now instead of null, how can I replace everything < 0 with 0.

I would like to dynamically replace all values under 0 (nagtives should be zero).

[robrayborn]

A most excellent demonstration!
How would this work when using data from File, from Folder, where the columns of data in the files may be different dates? Also while bringing in the 'Date created' from the file?

[Simon-vcwk]

Nice Tip. Another thought, what happens if the position of the range changes can this be updated dynamically for the import.

[ohhhnelly]

Excellent video! Instead of hard coding the original and replaced values (null and 0 in this example) in the formula, how can you replace values referencing a 2 column table in another query?

[txreal]

Would you have time to do a Power Query Bulk Replace Values video?
I've seen some other videos. However, I believe you may have a more elegant solution or less steps. ✌