Skip to content

Instantly share code, notes, and snippets.

@ncalm
Created September 8, 2022 01:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ncalm/68f8d403cde24e416a71fd82c7057df0 to your computer and use it in GitHub Desktop.
Save ncalm/68f8d403cde24e416a71fd82c7057df0 to your computer and use it in GitHub Desktop.
This Power Query function will apply a list of text substitutions to multiple columns in a table of dirty data
/*
Pass the names of tables in your Excel file to the first two parameters.
The substitutions table must have three columns:
1) column name
2) from text
3) to text
Exact column headers aren't important.
The delimiter should be some single-character delimeter that isn't present in the substitutions table.
For details:
https://www.youtube.com/watch?v=dIpa4nU9oIU
*/
(dirty_table_name as text, substitutions_table_name as text, delimiter as text) =>
let
//get the data from the two tables whose names were passed to the parameters
wb = Excel.CurrentWorkbook(),
dirty = wb{[Name=dirty_table_name]}[Content],
subs = wb{[Name=substitutions_table_name]}[Content],
//transform the substitutions table to a list of lists
subs_list = List.Transform(
Table.ToList(subs,Combiner.CombineTextByDelimiter(delimiter)),
each Text.Split(_,delimiter)
),
//iterate through the list of lists and apply the transformations
//to the appropriate columns
result = List.Accumulate(
subs_list,
dirty,
(a, b) => let
fn = (txt) => Text.Replace( txt , b{1} , b{2} )
in Table.TransformColumns(a , { b{0} , fn })
)
in
result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment