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.
Casually watching UA-cam 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!
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.
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 🙂
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?
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.
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.
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?
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
Wow great video! Thank you! You solved 1/2 of my situation by considering the possibility of additional columns. Now I need to replace a cell value in the 2nd row with a value from the 1st row or I need to move the value from the 1st row into the value in the 2nd row. Both values are in the same column, A1 A2 I believe adding a Index Column is a step. Any ideas? Thank You for your consideration.
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
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?
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/
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?
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
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. ✌
Have you explored if DAX Measures built referencing “original” column names, would still suffer model breaking on refresh where the Headers List has changed?
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.
@@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.
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.
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.
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?
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/
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".
Maybe add a custom column with this: =if Text.StartsWith( [ColumnOfWords],"Product") then Text.Replace([ColumnOfWords],"Product","") else [ColumnOfWords]
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?
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.
Glad it helped. I appreciate you taking the time to let me know you found it useful
I skill don't "grasp" the full power of Lists - but each one of your videos brings me closer
It's a slow and steady journey Gary
Casually watching UA-cam 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!
That's great Bruno! Thanks for letting me know
So simple and so very helpful.
I am glad to see the intellisense annoyances pointed out. Hopefully they get adjusted soon
Cheers Grainne
Nice, exactly what I have been dealing with hitting refresh and broken headers. Thank you.
You're welcome. Thanks for taking the time to leave a comment
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.
Beautifully done sir. Very applicable in a variety of scenarios
Cheers Shadrack, I appreciate your comment
Agreed, was just going to state the same thing.
@@davidferrick Thankyou!
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 🙂
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.
Brilliant.! Really useful and applicable solution for a usual problem... , thanks!!
Thanks for taking the time to leave a comment Antonio. 👍🏼
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?
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.
@@AccessAnalytic Thank you. I'll give it a go.
Love this little trick. Handy in when one is sourcing Rest api from all sorst of webservices.
Thanks for letting me know you found it useful VikingGuard
So many hours of work I would have saved if I new this before
This happens to us all Khalid 😀
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.
Glad to help. What were you looking for specifically?
That was awesome! Nice explanation of the steps required.
Thanks for taking the time to leave a kind comment
very elegant solution
Thank you
so lovely and useful
Thanks for the kind comment
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.
Thanks, does this help? chandoo.org/wp/multiple-find-replace-list-accumulate/
or this
www.howtoexcel.org/bulk-replace-values/
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).
I’d go with adding a conditional column and then remove the original column
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?
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
Gerat. Really useful for me.
Glad it helped Erica
Excellent tip, thank you so much!!!
Cheers Daniel, thanks for letting me know you liked it
Great video, as always
Thanks Monica
Awesome tip! Thank you!
Great, glad you liked it
Great video
Thanks Imran
This is awesome. Wow
Glad you like it!
Wow great video! Thank you! You solved 1/2 of my situation by considering the possibility of additional columns. Now I need to replace a cell value in the 2nd row with a value from the 1st row or I need to move the value from the 1st row into the value in the 2nd row. Both values are in the same column, A1 A2 I believe adding a Index Column is a step. Any ideas? Thank You for your consideration.
No worries. How many rows of data do you have?
Nice Tip. Another thought, what happens if the position of the range changes can this be updated dynamically for the import.
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
Are these files available for download? Would like to demo to my students. Greetings from South Africa.
Hi, not this one sorry. I’ve started including files with more recent videos
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?
Yep regardless of how the data is connected to the Power Query steps are then the same and will rerun each refresh
Very helpful...Thank's
Thank you Jorge
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?
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/
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?
Not sure off the top of my head. Any particular reason you want to avoid adding a column temporarily?
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
You could do a Transform to Multiply all values by 0 then replace the 0s? Would that work for you?
Can I use it if I have other steps like Navigation and Promoted Headers? because its not really working for me
Yes. Navigation and promote headers do not reference columns ( in the formula bar ).
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. ✌
Noted
Useful. Thanks!
Cheers Luciano
very good
Thanks Felipe
Have you explored if DAX Measures built referencing “original” column names, would still suffer model breaking on refresh where the Headers List has changed?
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.
@@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.
@@waynekranz7813 - excellent. Don't judge me on the sound quality in earlier videos!
great content!
Cheers André
Awesome,,, thank you 👍
You’re welcome Bashir. Thanks for the comment
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.
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
@@AccessAnalytic Thanks!
Can we apply this in replacement based on condition????
Can you give an example
Cool. Thank you a lot.
You’re welcome Boris
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.
Change to text if not sure what the data type is going to be
@@AccessAnalytic if i change to text, is there will be any problem with the data? .
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.
@@AccessAnalytic thank u , appreciate. Example my data : 3/32, 4/64, 12/1TB, any blank and null values.
That will be fine as Text
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?
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/
@@AccessAnalytic Thank you very much! I will check and hopefully find solution.
Nice, thanks :)
You’re welcome
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".
Maybe add a custom column with this:
=if Text.StartsWith( [ColumnOfWords],"Product") then Text.Replace([ColumnOfWords],"Product","") else [ColumnOfWords]
amazing
Cheers
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?
That’s difficult to answer without fully understanding the scenario . Maybe this will help? ua-cam.com/video/yXxHqD2p6JE/v-deo.html
Lovely tip :)
Thank you Paul
This is awesome
Thanks Alice
Useful
Thanks
Pity tables in model doesn't have the same option as in Excel Pivot to replace nulls with 0
Yep, although you can do that with the Pivot Table output from the model
thx alot and i hope to explain how to Skip rows empyty rows dynamicaly and remove columns that i donot neet easly way
Hi Ahmed There’s a remove blank rows button, and use the choose columns button to deselect columns you don’t need
👍
its a kind of magic!!! :D
Cheers FrankWhite
💯👍