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
I refer to a blog article by Gil Raviv around avoiding directly referencing columns when detecting data types: datachant.com/2018/05/14/auto...
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
/ wynhopkins
Twitter
/ wynhopkins
Access Analytic Blog
accessanalytic.com.au/blog

[@rajeshmajumdar4999]

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.

[@AccessAnalytic]

Glad it helped. I appreciate you taking the time to let me know you found it useful

[@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.

[@garylhaas2005]

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

[@AccessAnalytic]

It's a slow and steady journey Gary

[@GrainneDuggan_Excel]

So simple and so very helpful. I am glad to see the intellisense annoyances pointed out. Hopefully they get adjusted soon

[@AccessAnalytic]

Cheers Grainne

[@joescanlonh1365]

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

[@AccessAnalytic]

You're welcome. Thanks for taking the time to leave a comment

[@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.

[@AccessAnalytic]

Glad to help. What were you looking for specifically?

[@jazzista1967]

very elegant solution

[@AccessAnalytic]

Thank you

[@antoniogarrido3058]

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

[@AccessAnalytic]

Thanks for taking the time to leave a comment Antonio. 👍🏼

[@doviedk]

That was awesome! Nice explanation of the steps required.

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@lukesteen157]

Awesome tip! Thank you!

[@AccessAnalytic]

Great, glad you liked it

[@danielgalloecheverri2770]

Excellent tip, thank you so much!!!

[@AccessAnalytic]

Cheers Daniel, thanks for letting me know you liked it

[@brunof.s.8186]

Casually watching KZbin 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!

[@AccessAnalytic]

That's great Bruno! Thanks for letting me know

[@singhalmonica]

Great video, as always

[@AccessAnalytic]

Thanks Monica

[@VikingGuard]

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

[@AccessAnalytic]

Thanks for letting me know you found it useful VikingGuard

[@yousefissa100]

so lovely and useful

[@AccessAnalytic]

Thanks for the kind comment

[@EricaDyson]

Gerat. Really useful for me.

[@AccessAnalytic]

Glad it helped Erica

[@ImranHussain-xv4se]

Great video

[@AccessAnalytic]

Thanks Imran

[@biljanamajstorova3332]

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 🙂

[@AccessAnalytic]

Absolutely Bilijana. So many options. One thing to note is that unpivotting removes nulls so there would be nothing to replace hence my approach here.

[@jorstube]

Very helpful...Thank's

[@AccessAnalytic]

Thank you Jorge

[@Luciano_mp]

Useful. Thanks!

[@AccessAnalytic]

Cheers Luciano

[@MichaelBrown-lw9kz]

This is awesome. Wow

[@AccessAnalytic]

Glad you like it!

[@bashirmoustafa6116]

Awesome,,, thank you 👍

[@AccessAnalytic]

You’re welcome Bashir. Thanks for the comment

[@shadrackawunyo7786]

Beautifully done sir. Very applicable in a variety of scenarios

[@AccessAnalytic]

Cheers Shadrack, I appreciate your comment

[@davidferrick]

Agreed, was just going to state the same thing.

[@AccessAnalytic]

@@davidferrick Thankyou!

[@AndreTMaciel]

great content!

[@AccessAnalytic]

Cheers André

[@khalidmijhad5747]

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

[@AccessAnalytic]

This happens to us all Khalid 😀

[@alicemorili1330]

This is awesome

[@AccessAnalytic]

Thanks Alice

[@gborka]

Cool. Thank you a lot.

[@AccessAnalytic]

You’re welcome Boris

[@felipesignorellireis7839]

very good

[@AccessAnalytic]

Thanks Felipe

[@paspuggie48]

Lovely tip :)

[@AccessAnalytic]

Thank you Paul

[@raramaeja884]

amazing

[@AccessAnalytic]

Cheers

[@johnhackwood1568]

Nice, thanks :)

[@AccessAnalytic]

You’re welcome

[@JonathanExcels]

Useful

[@AccessAnalytic]

Thanks

[@julie_chen]

👍

[@Simon-vc1wk]

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

[@AccessAnalytic]

If the Excel data isn't in a named range or table and the "block" of data moves then Power Query may or may not pick that up automatically. If extra data has been added above or to the left of the block then things will likely get tricky / fail

[@FRANKWHITE1996]

its a kind of magic!!! :D

[@AccessAnalytic]

Cheers FrankWhite

[@rrrraaaacccc80]

💯👍

[@sabarashid1513]

Hi Wyn, thank you for sharing all the tips and tricks around Power Query. I have a problem slightly different - what if I want to replace all the non-null values with 1 multiple column. As we do in excel find * replace 1. Don't want to add helper columns to achieve this.... Can you please guide. Thanks again

[@AccessAnalytic]

You could do a Transform to Multiply all values by 0 then replace the 0s? Would that work for you?

[@ohhhnelly203]

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?

[@AccessAnalytic]

You can right-click “drill down” on the values you need to turn them into “parameters” and the use those named parameters instead of the hard coded values

[@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.

[@AccessAnalytic]

Thanks, does this help? chandoo.org/wp/multiple-find-replace-list-accumulate/ or this www.howtoexcel.org/bulk-replace-values/

[@kingLostSouls]

Will this work with an SQL Query ? so with every refresh will the function run to find any new null and replace with what was designated?

[@AccessAnalytic]

Yep regardless of how the data is connected to the Power Query steps are then the same and will rerun each refresh

[@txreal2]

I'm delighted to have subscribed & learned from your videos. I've been troubled with repeating the steps of replacing "(", " )", and "-" in home, work, and mobile phone # columns individually (3x3) in my csv. Is there a way to applied those 3 steps to those 3 columns in one sweep? Thanks.

[@AccessAnalytic]

If you select the 3 columns ( holding Ctrl ) then do the 3 replace values then you should be good. There’s no real need to do it one step

[@txreal2]

@@AccessAnalytic Thanks!

[@robrayborn1349]

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?

[@AccessAnalytic]

You would need to Unpivot Other Columns in the Transform Sample file when consolidating to allow you to stack a single date column on top of each other. Within the Remove Other Columns step you could maybe see if Date Created is one of the removed columns and then change the code not to remove it.

[@robrayborn1349]

@@AccessAnalytic Thank you. I'll give it a go.

[@defaultHandle1110]

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).

[@AccessAnalytic]

I’d go with adding a conditional column and then remove the original column

Marvelous!. Thank you. And now a challenge. I have a table with n months how heading. They store the amount of product to buy. I have a price column per product. I want to replace the monthly quantity columns with values multiplying each column of (amount month) * price. Without creating additional columns. The quantity and names of the columns is dynamic. What do you think?

[@AccessAnalytic]

Hi Huber, maybe a combination of Unpivot, and merge. Difficult to say without seeing the data. I'd suggest you post a few screenshots and if possible a dummy file here: aka.ms/excelcommunity or community.powerbi.com/

[@irenegurne8953]

Thanks but im thinking on how about changing a value of a record, depending on the seach condition, say i want to change the date of inv215? how would you do the seach , then change the date without creating additional column?

[@AccessAnalytic]

Not sure off the top of my head. Any particular reason you want to avoid adding a column temporarily?

[@engahmedelbendary1566]

thx alot and i hope to explain how to Skip rows empyty rows dynamicaly and remove columns that i donot neet easly way

[@AccessAnalytic]

Hi Ahmed There’s a remove blank rows button, and use the choose columns button to deselect columns you don’t need

[@waynekranz7813]

Have you explored if DAX Measures built referencing “original” column names, would still suffer model breaking on refresh where the Headers List has changed?

[@AccessAnalytic]

Power BI measures should update to reflect the new column names (e.g. if you have a measure = SUM( SalesData[Sales Value]) and in Power Query you rename the column from Sales Value to Sales $ Column, the measure will update to reflect this.

[@waynekranz7813]

@@AccessAnalyticSince watching I have had an opportunity to test that renaming intelligence of column names referenced in Measures do indeed carry through, even where a power Query table references a different source table in the model, where that original table has a header change. Brilliant assurance. Thanks for quick response and for years of quality video content. I’m currently enjoying working my way through your entire back catalogue on this channel for extra tips and gems.

[@AccessAnalytic]

@@waynekranz7813 - excellent. Don't judge me on the sound quality in earlier videos!

[@txreal2]

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. ✌

[@AccessAnalytic]

Noted

[@rafal2934]

Can I use it if I have other steps like Navigation and Promoted Headers? because its not really working for me

[@AccessAnalytic]

Yes. Navigation and promote headers do not reference columns ( in the formula bar ).

[@murtazaiqbal7267]

How can we replace values with wild card in power query.? e.g. if Any thing starts with "Product" will replace with null. Only values which starts with "Product".

[@AccessAnalytic]

Maybe add a custom column with this: =if Text.StartsWith( [ColumnOfWords],"Product") then Text.Replace([ColumnOfWords],"Product","") else [ColumnOfWords]

[@vernonlasrado]

That’s a wonderful code 👍 Is there any easier quick fixes for cleaning up the description field in bank statement that have multiple combinations of transaction ID, payee name, biller name etc?

[@AccessAnalytic]

That’s difficult to answer without fully understanding the scenario . Maybe this will help? kzbin.info/www/bejne/r4nbeaR6Z9VpgKc

[@ExcelWithChris]

Are these files available for download? Would like to demo to my students. Greetings from South Africa.

[@AccessAnalytic]

Hi, not this one sorry. I’ve started including files with more recent videos

[@kuldar.]

Greate video, Sir! I have a huge problem - tried to find solution but without any luck. I have merged query with inserted columns that include hard coded values. Every time after refreshing the query all inserted values have gone. Is there some way to keep these values after data refresh?

[@AccessAnalytic]

Thanks Kuldar86, if you are adding manual values to the table that has been loaded into the Excel worksheet then each time you refresh these will be replaced or at best show up in the wrong position and this should be avoided. Matt Allington does showcase a possible technique here exceleratorbi.com.au/self-referencing-tables-power-query/

[@kuldar.]

@@AccessAnalytic Thank you very much! I will check and hopefully find solution.

[@agustriyono]

Hello, nice video. i want to replace null values and blank in power query, but i don't know the data type. the data type is any. so if i used right click and then the replace values is off.

[@AccessAnalytic]

Change to text if not sure what the data type is going to be

[@agustriyono]

@@AccessAnalytic if i change to text, is there will be any problem with the data? .

[@AccessAnalytic]

Everything in that column will be treated as text, even numbers. So you won’t be able to add those numbers or do calculations with them.

[@agustriyono]

@@AccessAnalytic thank u , appreciate. Example my data : 3/32, 4/64, 12/1TB, any blank and null values.

[@AccessAnalytic]

That will be fine as Text

[@Simon-vc1wk]

Pity tables in model doesn't have the same option as in Excel Pivot to replace nulls with 0

[@AccessAnalytic]

Yep, although you can do that with the Pivot Table output from the model