ORDER BY items must appear in the select list if SELECT DISTINCT i

[ChrisE15890c]

When in the Transform Data editor and slecting a column and then telling it to load more data I am seeing the following error:

[DataSource.Error] Microsoft SQL: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

The data source is a MS SQL Server view where there is no Order BY being used. Views do not use Order By.

The problem is I am seeing more than one distinct value for this column of data.

Thanks

Chris

[Anonymous]

Hi  @ChrisE15890c ,

Can you provide detailed version information so we can reproduce the issue? We'll get back to the product team as soon as it's acknowledged as an issue!
Thanks in advance!

Best regards.
Community Support Team_Caitlyn

[LucasPanas]

Hi,

Same here :

let
Source = vwMetricsByItemandOperationandDaySnapshot,
#"Removed Other Columns" = Table.SelectColumns(Source,{"OperationName"}),
#"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates1", "Custom", each if Text.Contains([OperationName],"Refresh") = true then 1 else 0),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Refresh"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns")
in
#"Removed Duplicates"

[ChrisE15890c]

Hi community support. Per your request here is my version information. Also I wanted to update that I was able to resolve the issue by performing either a Trim Text or a Clean text transformation then click the drop down and the error was no longer present. They both resolved the issue for my specfic case. See screenshots below.

Version: 2.129.1229.0 64-bit (May 2024)

With error prior to Trim or Clean:

After Trim or Clean steps:

Thanks

Chris

[JL0101]

I have a similar issue, see code below 

let
Source = Sql.Databases("xxx.database.windows.net"),
Hink = Source{[Name="Hk"]}[Data],
dbo_boq_site = Hk{[Schema="dbo",Item="boq_site"]}[Data],
#"Sorted Rows" = Table.Sort(dbo_boq_site,{{"site_id", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"site_name_short"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates"

Error produced 

DataSource.Error: Microsoft SQL: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Details:
DataSourceKind=SQL
DataSourcePath=xxx.database.windows.net;Hk
Message=ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
ErrorCode=-2146232060
Number=145
Class=15
State=1

Version: 2.128.751.0 64-bit (April 2024)

[androo235]

Me too.