Using PowerQuery to find and replace words, telephone numbers etc

[bearcub]

We have a database that we need to reformat to be able to through into Access. I need change the spelling of addresses to conform to certain parameters (e.g. dr instead of drive or dr., st instead of street, ln instead of lane, (650) instead of 650 or 650-.etc). The data needs to be changed i can't get a round that. The solution is to create a series of macros that will do a find and replace for 67000 rows of data or use powerquery to transform the data. I haven't used PQ enough to know if this can be done.

I'm thinking that I can do this in PQ, it will save me a lot of time creating and running the macros ever time I have to upload a new file (like once a week).

the files I'm working with are from an external DB so the file size tends to be just under 12M for binary files (after I convert it from an xlsx and close to 20M for Xlsx files. Can PQ find and replace words, reformat telephone numbers and zip codes to the correct input mask?

Thank you for your help,

michael

 

[MarcelBeug]

In general, you can replace a series of values by other values using List.Accumulate, like in the query below, in which:
"Replacements" is a table with 2 columns: old and new value.
"Table1" is a table with column "Some Text" of which (parts of) values must be replaced according to "Replacements".

"Replacements" is first converted to a list of lists, so it can be used as first argument for List.Accumulate.
"Table1" is buffered to improve performance when List.Accumulate is executed.

List.Accumulate has 3 arguments, in this case:
- the list with replacements
- the initial value (buffered table)
- the accumulator function.

List.Accumulate loops over the list, and with each iteration, performs the action as defined in the 3rd argument.
This 3rd argument is a function with 2 arguments:
- the first one refers to the second argument of List.Accumulate (i.c. "t" for table) and
- the second one refers to the first argument of List.Accumulate (i.c. "r" for replacements; r{0} is the old value; r{1} is the new value)

let
    ReplacementList = Table.ToRows(Replacements),
    BufferedTable = Table.Buffer(Table1),
    ReplacedValues = List.Accumulate(ReplacementList,BufferedTable, (t,r) => Table.ReplaceValue(t,r{0},r{1},Replacer.ReplaceText,{"Some Text"}))
in
    ReplacedValues

However, it looks like your replacements are more complicated and I'm not sure if this general approach can be translated to your case.

 

[bearcub]

I would be replacing words like Street with St, Drive with Dr, etc - whatever would show up in an address. I would also have to replace commas and periods with a null so it would be rather complicated. Whatever I can do in powerquery means less I would have to do with macros - I don't have an option. One or the other.

Where would I enter this code you mentioned?

Michael

 

[MarcelBeug]

You can create a new blank query, enter the Advanced Editor, and replace the default code by the code above.

Note that you also need to load tables Table1 and Replacements into Power Query.

 

[bearcub]

How many replacements can I make? Yesterday I was going through the telephone field and have over 30 different formats. One of these we are going to keep and we will have to change the format of the others to conform to that one. Is there a limit as to how many replacements I can make?

 

[MarcelBeug]

The only limitation would be the number of rows in Excel (1 million plus).

You may wonder if this is the right approach for all replacements, as this is about replacing (sub)strings and not about transforming formats.
Possibly you should do some replacements this way and otherwise use some formulas for other transformations.

If you should need more specific help, then you need to share some representative (maybe fictitious) data.