Type Any in Power Query

[Omid_Motamedise]

When you load data from Excel into Power Query, removing the "Change Type" step results in the columns being treated as "Any" type. If we sort a column of type "Any," Power Query applies different sorting logic depending on the values in the column. If the values are numbers, it sorts them as numbers; if the values are text, it sorts them as text; and if the values are dates, it sorts them as dates.

I’m wondering what happens in practice in this case. Does Power Query first detect the data type and then sort based on the detected type, or is there a general rule for sorting data when it's in "Any" type?

[ZhangKun · Accepted solution]

no any. This experience found from parctice.

[v-ssriganesh · Accepted solution]

Hi @Omid_Motamedise,

I hope you're doing well! I wanted to follow up on your query regarding Power Query and the behaviour of the "Any" data type.

As @ZhangKun correctly pointed out, Power Query sorts values based on the specific type of each value instead of the column type when dealing with "Any" values. This sorting behavior is based on practical experience, and unfortunately, there isn’t a specific Microsoft document that outlines it.

Have you resolved your issue with sorting in Power Query, or is there anything else related to your query that you need assistance with? If this resolves your issue, please accept it as a solution and leave a "Kudos" so other members can find it more easily.

Thank you.

[ZhangKun]

in practice, u will be found that powerquery first checks the type of value(not column type), and then sorts the values of same type together. This means that there is a priority between the different types.

[Omid_Motamedise]

Thanks, @ZhangKun, for the response.
Is there any reference in Microsoft Document to explain such behavior of commands over the column in type any?

[ZhangKun · Accepted solution]

no any. This experience found from parctice.