Data Validation and Error Handling in PowerQuery

Hi S,

Your approach to validating the data using Power Query M script seems reasonable. However, there might be some room for optimization to improve performance. Here are a few suggestions:

Parallelize the validation process: If possible, try to parallelize the validation process by splitting the dataset into smaller chunks and validating them concurrently. This can help to speed up the overall process.

Optimize the M script: Review your M script for any redundant or unnecessary calculations that might be slowing down the process. You can also consider using native M functions instead of custom functions, as they are usually faster.

Use buffering: If your dataset is large, consider using the function to cache the data in memory. This can help to speed up the validation process by reducing the number of times the data is read from the source.

Performance tip for List.Generate (1): Buffer your tables in Power BI and Power Query

Optimize lookup tables: Make sure your lookup tables are optimized for quick lookups. You can use the function to sort the lookup tables by the key column, which can help to speed up the function.Table.SortTable.Contains

Remember that performance optimization is an iterative process, and you might need to try different approaches to find the best solution for your specific use case.I hope these suggestions help you improve the performance of your data validation process. If you have any further questions, please feel free to ask.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum