Offset vs power query helping table for data validation

[JulieCooper]

Hi, 

I am creating a template that will be duplicated repeatedly (at least five times per week) and saved on our shared drive (SharePoint). Currently, the template is about 10MB. 

I use 3 data validations in the template, and I can either use a dynamic array that uses an offset formula or load a number of tables generated with a power query and use an IF function for data validation (both options using name manager). I tested these options and both work. 

The offset solution is more straightforward, but I understand that, as a volatile formula, it may not be my best option. Does anyone have any recommendations as to which option I should use for this template?

[v-xinruzhu-msft · Accepted solution]

Hi @JulieCooper ,

Based on your description, it is better that use power query for the data validation.

1.for the offset() function, it is based on the relative position.  if your position change the function will recalculate, it will affect the performance.

2.Power query can provide the data validation,it can improve performance and maintainability of your template and it can also provide other functions, such as data transform, it offer more choice for you. 

3.For the large dataset, in power query, you can use query folding so that it can improve the performance, you can refer to the following link.

Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.