Is there a While or Until condition in PQ?

[sysmod]

I am writing code to automatically use Workbook Queries in Powerquery to open CSV files, so I am not working through the UI. I write M code in VBA and use that for the query.

I have a method to scan an entire column and infer what the data type is most likely to be.

https://sysmod.wordpress.com/2021/10/28/power-query-infer-data-types-with-table-profile/

That's because PQ has no InferDataType method exposed, although it does determine the type itself if invoked from the UI.

However, it is slow because it runs Expression.Evaluate() on the entire column for all four possible data types - number, date, datetime, text.

One of the parameters is the proportion of errors allowed - eg 0.01 is 1%

Is there a way to shortcut this so the loop stops once the number of errors exceeds this threshold, so that this possible type can be eliminated?

TIA

Patrick

[ImkeF · Accepted solution]

Hi @sysmod ,

the List.Generate function has such a condition in its 2nd argument. But to avoid re-evaluation of the iterated table in each step, you have to use a Table.Buffer. That itself can be a killer on large datasets. So you have to find a balance here. I believe that this is the main dilemma we have in PQ that will prevent any dynamic type detection as you're envisioning running fast on large datasets.

Also, I am not using Table.Profile on large datasets, because the calculation of the standard deviation in it makes it slow. Instead I apply List aggregators for each column only for the fields I really want to see.