How to Replace URL Encodings in Power Query Using a Reference Table
Question details
The user needs to decode percent-encoded URL characters in a specific column (such as LinkURL) by referencing a separate table that contains the encoding pairs.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Cleaning web data where URLs contain percent-encoded characters (like %20 for a space), requiring a dynamic bulk replacement using a maintained reference table.
- Observed behavior
- Using Table.TransformColumns combined with List.Accumulate correctly applies the batch replacements, provided the column exists and headers are properly promoted beforehand.
Ensure you have your main dataset loaded into Power Query and a separate 'Encoding Reference' query prepared with at least two columns: the encoded string (e.g., '%20') and the decoded character (e.g., a space).
Use List.Accumulate to Bulk Replace URL Encodings
Use this method to apply multiple text replacements from a reference table to a target URL column dynamically in your M code.
This approach uses the List.Accumulate function to iterate over your encoding reference table, finding and replacing every matching encoded string in the target column with its decoded equivalent.
First, ensure your main dataset's headers are promoted. This ensures the target column (e.g., 'LinkURL') is recognized by its proper name rather than a default name like 'Column19'. Add a Table.PromoteHeaders step if it is missing.
In your Advanced Editor, add a step to zip your reference table columns into a list of pairs. For example: `Replacements = List.Zip({#"Encoding Reference"[#"From UTF-8"], #"Encoding Reference"[Character]})`.
Use Table.TransformColumns with List.Accumulate to iterate through the replacement pairs. Add the step: `Table.TransformColumns(PromotedHeaders, {{"LinkURL", each List.Accumulate(Replacements, _, (current, pair) => Text.Replace(current, pair{0}, pair{1}))}})`.
Click 'Done' in the Advanced Editor. Check your LinkURL column to confirm that encoded strings like '%20' have successfully been replaced with blank spaces and other decoded characters.
Use WPS Office for Efficient Data Management
While advanced M-code scripting is specific to Excel's Power Query, WPS Office provides a free, lightning-fast alternative for managing and cleaning large spreadsheets. Experience seamless Excel compatibility and powerful built-in text processing tools.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx or .csv dataset with guaranteed compatibility.
- 3. Process Your Data: Use powerful built-in functions, regular expressions, or batch Find and Replace to clean your URL data without complex scripting.
Frequently Asked Questions
Why do I get an error saying the 'LinkURL' column wasn't found?
This usually happens if the transformation step is executed before the dataset headers are promoted. Ensure you apply the Table.PromoteHeaders function prior to referencing the 'LinkURL' column in your script.
Can I use this List.Accumulate method to replace other text strings besides URL encodings?
Yes, the List.Accumulate approach paired with a reference table is a versatile method for bulk replacing any text strings in Power Query, such as normalizing company names, fixing typos, or removing special characters.
How exactly should my encoding reference table be structured?
Your reference table should be loaded into Power Query with at least two columns. One column should contain the target string you want to find (e.g., "%20"), and the other must contain the desired replacement value (e.g., " ").
