Power Query: How to mass replace values based on a list | Excel Off The Grid
Вставка
- Опубліковано 4 сер 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/power-que...
★ About this video ★
A few weeks ago, I posted about using Excel's REDUCE and SUBSTITUTE functions to perform multiple find and replace actions to create dynamic text.
But what if we want multiple find and replace during the data-cleaning process? In that scenario, we need to use Power Query to replace values based on a list. So, in this post, let's find out how to do that.
CONTENTS
0:00 Introduction
0:46 Replace values based on a list
6:59 Conclusion
Link to post by Rick De Groot:
gorilla.bi/power-query/list-a...
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Super clear and easy to follow. Thank you so much.
Glad it was helpful!
Thanks Mark! This is exactly what I was looking for :)
This is a great tutorial! Thank you so much. This is exactly what I am looking for.
You're welcome! I’m glad it was helpful.
The magic has returned. Thanks Mark. Excellent and complete explanation.
Many thanks!
I will use this technique all the time. Thanks Mark.
Excellent!
This is extremely useful to clean up bad data from multi sources! Bravo!
Thanks, I'm glad you found it useful. 😁
Excellent tutorial.
Many thanks!
Thanks for another brilliant video on Power Query techniques for data cleansing and transformation. It's extremely useful and very clearly explained. Thank you so much.
Glad it was helpful!
Many thanks for an excellent video, this was just what I needed to solve the problem I was working on 😊One small improvement would be to make it an exact match, i.e. so if changing from "Wicks" to "Wicks Ltd", and then re-running the query at a future date, it would ignore the lines already correctly named as "Wicks Ltd" rather than making them "Wicks Ltd Ltd". This can be achieved by changing the "ReplaceText" to "ReplaceValue" at the end of the List.Accumulate function. Thanks again, and I have subscribed to the channel, as there are some really good tips and tricks videos, especially for Power Query 😊👍
You can apply whatever settings you want within the Replace Values dialog box - it just depends on your scenario.
Very nicely explained List.Accumulate Function.
Thanks a lot 😁
This is another excellent video from Mark! I feel List.Accumulate function is the most tricky one in M code and one of the few functions with looping effect. Usually one has to study its structure and working mechanism for quite some time before fully understanding it. Mark explains this very clearly and to the point in one go using this great example. Mark skillfully used the M code generated from a designed step as ramp and used it as the key part of the accumulate function element. This not only makes the reader to understand the structure of the List.Accumulate function easily but also paves they way for better understanding of the target this function to achieve in this case. The way Marks explains is both elegant and to the right point for good understanding of this function and its application in this case. Thanks Mark for sharing your wisdom always in the Power Query community 👍👍🌹🌹
Thanks Kebin.
In each video, I try to show the solution, but also teach the techniques that can be applied elsewhere. I'm glad that came across will in the video.
List.Accumulate is really confusing, often because it's approached technically. By having a real-world practical example, I hope it can simplify it.
I always check your videos, either by clicking on YT notification or redirecting from my mailbox, I find your videos very enriching but majority of the times, the magics look like rocket science to me. Thanks mate.
Thanks for that feedback, it's really useful.
How can I make it seem less like rocket science? Any ideas would be useful for helping me make better videos. 👍
Great! Very helpful, thanks Mark
Thanks, That's good to hear. 😁
Thanks. It helped me alot.
You're welcome!
This issue bothered me for ages, lot of replacement steps for nothing...but this is amazing. Thanks!!
You're welcome! Hopefully you can put it to good use.
Excellent Mark
Thanks Jim 😁
Excellent
Wow, this video is a game-changer! 🌟 After spending what felt like ages searching for a solution to this incredibly frustrating problem, your method came as a breath of fresh air. The time savings it offers are truly phenomenal. I can't thank you enough for sharing this gem. You've just earned a loyal subscriber! 🙌 #TimeSaver #LifeSaver
TLDR: The fundamental idea of the video is to use Power Query's "replace values" action and the accumulate function to quickly and efficiently clean up data by mass-replacing values based on a list.
1. 00:00 🔍 Clean up data by mass-replacing values based on a list of different ways to spell the same company name.
2. 01:17 💡 Use Power Query to easily replace values in a table by creating a list of items to find and their corresponding replacements and then using the "replace values" action in Power Query with the appropriate syntax.
3. 02:39 💡 The speaker explains how to use the list dot accumulate function in Power Query to replace values based on a list.
4. 03:22 📝 Create a list of numbers representing each item in a given list by starting from zero and counting up to the number of items in the list minus one.
5. 04:15 💡 The speaker explains how to use the seed and accumulator function to loop through a list and change values in a table.
6. 05:13 🔁 Each time we loop, the state will update based on the previous result.
7. 05:54 🔍 Use the "find" and "replace" columns in a table to mass-replace values in a dataset, resulting in clean data.
8. 06:57 💡 Replace multiple values in Power Query based on a list using the accumulate function for powerful looping functionality.
Thanks. I'm so glad you'll be able to use it and save a lot of time.😁
Great use of List.Accumulate.
Alternatively we can use this formula:
= List.Accumulate(Table.ToRecords(FindReplace),
#"Changed Type",
(state, current) => Table.ReplaceValue(state,current[Find],current[Replace],Replacer.ReplaceText,{"Name"}))
That is a nice alternative, very good 👍
What if there are multiple columns?
thank you i used it 8 times in my query replacing Merges and Change types so nice and clean now!
Amazing - Nice clean queries - good work.
Thanks for the awesome video
Thanks for watching! 👍
One of the clearest explanations of the Accumulate function I have seen. Thanks Mark.
Thanks 😁
This was exactly what I needed and presented in a short, concise, yet complete manner. Issues I found (being a complete noob at Power Query) was getting the data into Power Query and that the data is case sensitive (I obviously figured both of those out). Well done, well done indeed!
This really isn’t a noob level solution, so you’ve done well ✅
👏 amazing
Thank you.
You're welcome!
This is almost perfect 😃 Instead of matching the full Find string, I'd like to match a partial string, aka Text.Contains. Adding this to the function generates an error "cannot convert a value of type Function to type Text." Ideas?
Also keen to find a way of this working with a wildcard please
Well that was a surprise when that worked, I'd been trying for ages but using column headers as the list to accumulate as for transform, really ingenious. Now to replace them conditionally.
Glad it helped!
Gostaria de agradecer por ter compartilhado. Irá me ajudar muito. Parabéns!
My pleasure.
Great Tutorial.. but from practical perspective.. do you see any performance improvement on large data vs using merge technique to fetch the correct name by lookup to replacement table.
If you are replacing full words, then I think Merge would be faster (as merges are just faster in general). However merge won't work if you have partial words; so it depends on your scenario.
Alternate solution that may be easier to understand:
= Table.ReplaceValue(
#"Source table",
each [Column to replace],
each FindReplace{[Find=[Column to replace]]}[Replace],
Replacer.ReplaceValue,
{"Column to replace"}
)
Nice… very nice. Let me try this out. Thanks for sharing. 👍
Great video! I have a small problem that's causing an issue though - to simplify, if I have a column of data that contains the word "house" and I want to change this to "home", and I also have the word "warehouse" in the same column, I end up with an entry that's corrected to "home", but I also end up with "warehome" - is there a way to make sure the case and cell contents are matched and exact?
You can use if else or best switch statement
Excellent, easy to follow video, fantastic. Would be good to know if this is possible to be saved as a custom function, if you have multiple columns from multiple tables in your model that need replacing from the FindReplace table then it be useful to be able to just quickly call on a custom function to do this.
Yes, this can be a custom function.
However your idea.if transforming multiple tables at the same time would not be possible.
I can think of a way to do it, but it would be so complex that it would remove any benefit.
@@ExcelOffTheGridThanks for your reply.
I did think it would perhaps be to complex and take away any benefit.
So let's say it wanted to be used on just a single column, how would it be used as a custom function in this situation?
What an excellent video!!. Thank you for the teaching. Is there a way to consider null values as a "Find" criteria in order to replace it with a non-null value?
Yes, you need to make sure the Find value in Power Query is null and not “null”.
If the list is from an Excel Table initially, a blank cell is treated as null.
Thank you @@ExcelOffTheGrid for your response. The null value is the result of an expanded column from a combined query. What I intend to do is replacing the null values as well as the matching values from the list.
List.Accumulate(Table.ToRecords(SERVICES),
#"Columnas reordenadas1",
(state, current) => Table.ReplaceValue(state,current[From],current[To],Replacer.ReplaceText,{"SURGICAL"}))
This is the Replacing List
From To
Cx Loan Set NV SC DPS-LOANER
Cx Loan Set V SC DPS-LOANER
CX Consignment NV SC DPS-CONSIGNACION
CX Consignment V SC DPS-CONSIGNACION
Cx Mix - Loan & Cons NV SC DPS-MIX
Cx Mix - Loan & Cons V SC DPS-MIX
null PENDING
Just as a follow up I found that if replacing over multiple columns some of which did not contain text
i got errors to used a custom replacer, i stared by make virtual old /new table ;
let t = #table( {"old", "new"}, { {"sheep", "Goat"} , { "teddy", "paddington"} ,{ "mice", "rats"}} )
in List.Accumulate( {0..2} , Source, (S,C)=>
Table.ReplaceValue( S, t [old] {C}, t[new] {C},
(x,y,z)=> if Value.Type(x) = type text then Replacer.ReplaceText( x, y, z ) else x ,
Table.ColumnNames(Source) ) )
Sweet Mark! Have you tested the performance of this on large data sets? Just wondering if it's slower or faster than a bunch of "find-n-replace" steps...
In terms of performance, there should be little to no difference.
List.Accumulate is applying the replace step multiple times inside a single step. Which is the same basic transformation as doing it across many steps.
Excellent video and instructions. How can I download the Excel practice file? I have a similar task to find and replace on 7800+ rows. thanks.
There is a link in the video description to the blog post. From there you can get access to the example file.
Hello, your Power Query videos helped me with important things. Could you help me? How to create multiply columns in the table using the Advanced Editor?. I created a function and it is called many times, which ends up thinking a little. Each column has its own name. And if you create the dynamic columns, based on a list of names, is it possible?
Yes, it’s possible. But why would you need to?
@@ExcelOffTheGrid see codes in power query
docs.google.com/spreadsheets/d/1ikCgWe1DLBSA_pRfgvnewJU-es5T_UIO/edit?usp=drivesdk&ouid=107048917366575816913&rtpof=true&sd=true
= source (https: // docs.google.com/spreadsheets /d/1ikCgWe1DLBSA_pRfgvnewJU-es5T_UIO/ edit?usp=drivesdk&ouid=107048917366575816913&rtpof=true&sd=true)
code table:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"CUSTOMER", type text}, {"GROSS VALUE", Int64.Type}}),
// Here, the custom function is called for each column added. And if by chance, I want to make a different smoothie, it will be necessary to manually edit it in the code. How do I make this dynamic based on my dimension table list.
AddColumns = Table.AddColumn(#"Changed Type", "Data", each
let
col1 = Table.AddColumn(#"Changed Type", "GRAPE", each functionX([DATE], [DATE], "GRAPE") * [GROSS VALUE]),
col2 = Table.AddColumn(col1, "WATERMELON", each functionX([DATE], [DATE], "WATERMELON") * [GROSS VALUE]),
col3 = Table.AddColumn(col2, "LEMON", each functionX([DATE], [DATE], "LEMON") * [GROSS VALUE]),
col4 = Table.AddColumn(col3, "ORANGE", each functionX([DATE], [DATE], "ORANGE") * [GROSS VALUE]),
repleceerros = Table.ReplaceErrorValues(col4, {{"GRAPE", null}, {"WATERMELON", null}, {"LEMON", null}, {"ORANGE", null}} )
in
repleceerros),
#"Expanded Data" = Table.ExpandTableColumn(AddColumns, "Data", {"GRAPE", "WATERMELON", "LEMON", "ORANGE"}, {"GRAPE", "WATERMELON", "LEMON", "ORANGE"})
in
#"Expanded Data"
The solution presented in the video is certainly robust and elegant. I thought, however, that I could achieve the same results by using an outer left join merge query on the Data and FindReplace tables on [Name] = [Find].
Then I added a custom column ([Standardized Name]) with a formula like
= if [Replace] = null then [Name] else [Replace].
Then I kept just the [Standardized Name] and [Total] columns and finally renamed [Standardized Name] to just [Name].
Would this approach be as efficient as your M-Code solution? Are there advantages or issues with one approach over the other?
Thank you kindly.
I think using a merge would be faster if using full words.
However Replace Values doesn't have to be full words, it can replace partial words. Which a join can't do.
You could also consider Fuzzy Match, if you like to take risks 😁.
Therefore, I think they are sightly different solutions, which might have cross over in some use cases.
@@ExcelOffTheGrid Thank you for the clarification. I hadn't given thought to the need for partial matching but for such a use case the programmatic solution you demonstrated would be ideal. Thank you.
And you can just add to that table of findreplace to continue to find?
Yes, you’ve got it. You could have thousands of rows, might be a little slow, but it would work.
@@ExcelOffTheGrid thank you. Complicated but great function we can use thanks to ur tutorial.
Hi. Since only the list of values for the [Find] column was referenced in the beginning, how does the current moved to the [Replaced] column when declared later down the line?
Nice, another good example is "How to Merge Excel Files with Different Headers in Power Query | List.Accumulate" by Leila Gharani here on UA-cam. Supper confusing at first! 🙃🤨😕
Thanks for sharing!
How to change the texts in multiple columns
May i ask for your help i have a data with
Me1
Me2
should be replace with ME_yes
But the output is shows as "ME_yes_yes" instead of "ME_yes"
Hey Mark! I tried this on my dataset and received a stack overflow error :(
List.zip would work too here, right?
There are lots of ways to achieve this. List.Zip could definitely be used in a solution.
HELP....this doesn't match the entire cell contents, only partials, so I am getting really jumbled up results
In the Replace Values dialog box. Click the advanced options and apply the Match Entire Cell Contents option.
there's a bug in this method . If I have the word MURALE in Find to become MURALI in the Replace column, and then I have MURALETTO to become MURALETTI, it will change this last word in MURALITTI (with an "I" instead of an "E") because it doesn't search for the exact word, but it identifies the word MURALE inside of the word MURALETTO and will change it to MURALI even insede the word MURALETTO that becomes incorrectly MURALITTI.
Just change the advanced options sections of the Replace Values dialog.
Then you’ll get the code you need.
@@ExcelOffTheGrid thanks, i'll try that
For beginners its 100% over the head 😂😂😂
No, it’s not a beginner level solution.