I push about 30k records a week into OnBase for a county department and want something I can finish in a day that helps with field mapping and validation. Is Power Query the best bang-for-buck, or is there a simpler option that saves time without piling on extra tools?
I’d stick with Power Query and keep a tiny two-column “Mappings” sheet (source_name → onbase_name), then merge it and use “Rename Columns” so changing field maps is just editing the sheet. In the same query, set data types and use “Keep Errors” to dump a bad-rows view you can check before the import. If that still feels heavy for a one-day, OpenRefine’s clustering is a quick win, but PQ pays off by being repeatable.
For 30k/week, Power Query is the best one-day win — turn on data profiling (View > Column quality) and build an ‘Exceptions’ output… Do you have a fixed set of required fields and allowed values to check? Split Good vs Exceptions and feed only the clean table to OnBase; building on @mfox29’s mapping sheet, keep the map there but let PQ surface the column quality so tweaks take minutes, not hours.