Power BI Report Builder Distinct and Order By

[Pol]

Hi,

I would like to ask regarding to this query error that I'm encountered in power BI report builder I'm using an ODBC connection to my excel data

[v-kpoloju-msft · Accepted solution]

Hi @Pol,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @BhavinVyas3003, for your inputs on this issue.

I understand you are experiencing a syntax error in Power BI Report Builder when using SELECT DISTINCT with an ORDER BY clause via an ODBC connection to Excel.

This issue arises due to a limitation in the Microsoft ODBC Excel driver, which does not permit ORDER BY on columns not included in the SELECT DISTINCT clause.
 

Use only DISTINCT in SQL and apply sorting inside Report Builder:

SELECT DISTINCT OrderDate FROM [Sheet1$]

Then go to Dataset Properties > Sorting and add Order Date as a sort of expression. This moves the sort of logic to the report layer, bypassing SQL syntax restrictions.

Use GROUP BY with ORDER BY:

SELECT OrderDate FROM [Sheet1$]

GROUP BY OrderDate

ORDER BY OrderDate

This gives a unique list of dates and allows SQL-level sorting without conflict.  Pre-process the data using Power Query in Excel or Power BI Desktop before connecting it to Report Builder. This allows for complete control over sorting, transformations, and column structure beforehand.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Pol,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[BhavinVyas3003]

Check these options,

• Use only DISTINCT, then sort inside Report Builder:

SELECT DISTINCT OrderDate FROM [Sheet1$]

→ Set sorting in Report → Dataset → Properties → Sort by OrderDate.

• Use GROUP BY with ORDER BY

SELECT OrderDate FROM [Sheet1$] GROUP BY OrderDate ORDER BY OrderDate

• If possible, pre-process the Excel data in Power BI Desktop or Excel using Power Query, then publish or connect to that transformed file.