Data Validation and Error Handling in PowerQuery

Question

Hello people,&nbsp;i do have a source dataset thats generated from user inputs and therefore need to be validated before i can pass the dataset for analyzing.&nbsp;There are mostly two possible type of errors:- logical erros ( [Field1] = 0, [Field2] &lt;&gt; 0)- lookup errors ([Field3] value not in lookup/ list&nbsp;I wrote a powerquery M script that validates the data in three steps.1.&nbsp; "if then" statements&nbsp; to find logical erros, and "Table.Contains" functions to find data thats not in the lookup tables.&nbsp;i.e.E20 = if [Field1] = 0 and [Field2] &lt;&gt; 0 then 20 else null,E21 = if [Field3]&nbsp; = x and [Field4] &lt;&gt; y then 21 else null,E22 = if Table.Contains(tab_categories, [category = [out_category]]) = false then 22 else null,&nbsp;2.&nbsp; create a result list of all the conditions/ if clauses and remove null values&nbsp;results = List.RemoveNulls({E20,E21,E22)&nbsp;3. Translate those results into error messages by looking up each element of the result list in a tab_errorcodes and combine the texts:error_message = Text.Combine(List.Transform([results], each tab_errorcodes[ErrorMessage]{List.PositionOf(tab_errorcodes[ErrorCode], _,0)}))&nbsp;&nbsp;The script has to handle approx. 2k Datarows but seems to be very slow and produces timeout errors at some point.I am looking for tips/ hints to learn new ways/ best practices to get the task done and would love to hear some feedback to my approach.Greetings,S

anonymous · Accepted Answer

Hi S,
&nbsp;
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
&nbsp;
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.
&nbsp;
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Data Validation and Error Handling in PowerQuery

2 Replies

Related content

Data Validation - PowerQuery

Handling Null Values

PowerQuery transformation

PowerQuery Custom Column Lookup Calculation that handles blank values

Theme Validation Error