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 TH-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.
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.
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
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 🙂
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?
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.
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.
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
Yes. Navigation and promote headers do not reference columns ( in the formula bar ).
2 ปีที่แล้ว
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/
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]
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.
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. ✌
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.
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/
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?
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
So simple and so very helpful.
I am glad to see the intellisense annoyances pointed out. Hopefully they get adjusted soon
Cheers Grainne
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
Casually watching TH-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
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.
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
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.
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!
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?
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/
Brilliant.! Really useful and applicable solution for a usual problem... , thanks!!
Thanks for taking the time to leave a comment Antonio. 👍🏼
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
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
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
That was awesome! Nice explanation of the steps required.
Thanks for taking the time to leave a kind comment
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.
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?
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!
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
So many hours of work I would have saved if I new this before
This happens to us all Khalid 😀
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
Can we apply this in replacement based on condition????
Can you give an example
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 ).
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/
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
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]
so lovely and useful
Thanks for the kind comment
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?
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?
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
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
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
very elegant solution
Thank you
This is awesome. Wow
Glad you like it!
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
Thanks Imran
Great video, as always
Thanks Monica
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!
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.
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
Awesome tip! Thank you!
Great, glad you liked it
Very helpful...Thank's
Thank you Jorge
great content!
Cheers André
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? th-cam.com/video/yXxHqD2p6JE/w-d-xo.html
Awesome,,, thank you 👍
You’re welcome Bashir. Thanks for the comment
Useful. Thanks!
Cheers Luciano
very good
Thanks Felipe
Lovely tip :)
Thank you Paul
Cool. Thank you a lot.
You’re welcome Boris
Nice, thanks :)
You’re welcome
This is awesome
Thanks Alice
amazing
Cheers
Useful
Thanks
💯👍
👍
its a kind of magic!!! :D
Cheers FrankWhite