The specified sort criteria is invalid. Details: List

[a3]

I am new to Power bi. We made a connection with Exact Online through API. With a number of these API's we have a problem

Expression.Error: The specified sort criteria is invalid.
Details:
List

The script were are using is for Transactions and GLAccountClassifications the same as for GLAccounts and GLClassifications. For last two the script works OK.

Script for GLAccountClassifications in Advanced Editor:

let
Source = let

actualUrl = URL & "/api/oauth2/token",

cntnt = Text.ToBinary(Uri.BuildQueryString(

[refresh_token= Refresh_token

,grant_type="refresh_token"

,client_id= Client_id

,client_secret=Client_secret

])),

options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],

result = Web.Contents(actualUrl, options),

#"JSON" = Json.Document(result),

access_token= #"JSON"[access_token],

AccessTokenHeader = "Bearer " & access_token,

GetJsonQuery = (Web.Contents( URL & "/api/v1/beta/"& Number.ToText(DivisionCode) & "/" & URIH,

[Headers=[Authorization=AccessTokenHeader]])),

#"Imported XML" = Xml.Tables(GetJsonQuery,null,65001),

#"Changed Type" = Table.TransformColumnTypes(#"Imported XML",{{"id", type text}, {"updated", type datetime}}),

#"Expanded link" = Table.ExpandTableColumn(#"Changed Type", "link", {"Attribute:rel", "Attribute:title", "Attribute:href"},

{"link.Attribute:rel", "link.Attribute:title", "link.Attribute:href"}),

#"Expanded entry" = Table.ExpandTableColumn(#"Expanded link", "entry",

{"id", "title", "updated", "author", "link", "category", "content"}, {"entry.id", "entry.title", "entry.updated",

"entry.author", "entry.link", "entry.category", "entry.content"}),

#"Expanded entry.content" = Table.ExpandTableColumn(#"Expanded entry", "entry.content",

{"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "Attribute:type"},

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "entry.content.Attribute:type"}),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded entry.content",{"link.Attribute:title",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"link.Attribute:title"] = "GLAccountClassificationMappings")),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" =

Table.ExpandTableColumn(#"Filtered Rows", "entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata",

{"properties"}, {"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata",

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties"}),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties" =

Table.ExpandTableColumn(#"Removed Other Columns1",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"},

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c..."}),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c..."
= Table.ExpandTableColumn(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c...",

{"ClassificationCode", "ClassificationDescription", "Division"}, {"ClassificationCode", "ClassificationDescription", "Division"}),

#"Sorted Rows" = Table.Sort(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c...", Order.Ascending}})

in

#"Sorted Rows"
in
Source

Any help would be greatly appreciated!

[MarcelBeug · Accepted solution]

Your query structure disables the possibility to see the result after each step.

So my first advice would be to delete the very first part (let Source =) and the very last part (in Source), so you can step through the query in the Query Editor.

Your specific issue is with the step #"Sorted Rows", in which the part is missing on which column you want to sort.

A typcical code structure would be:

Table.Sort(MyTable,{{"MyColumn", Order.Ascending}})

The red part is missing in your code.

[a3]

You are right to change the sort criteria is the solution.

For the API Transactions we have the same problem. In the browser an $select is needed too run the api.

Our code inclusive the select statement:

let

actualUrl = URL & "/api/oauth2/token",

cntnt = Text.ToBinary(Uri.BuildQueryString(

[refresh_token= Refresh_token

,grant_type="refresh_token"

,client_id=Client_id

,client_secret=Client_secret

])),
options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],
result = Web.Contents(actualUrl, options),
#"JSON" = Json.Document(result),
access_token= #"JSON"[access_token],
AccessTokenHeader = "Bearer " & access_token,
GetJsonQuery = (Web.Contents( URL & "/api/v1/"& Number.ToText(DivisionCode) & "/" & URIL & "?$select=ClosingBalanceFC",

[Headers=[Authorization=AccessTokenHeader]])),

#"Imported XML" = Xml.Tables(GetJsonQuery,null,65001),

#"Changed Type" = Table.TransformColumnTypes(#"Imported XML",{{"id", type text}, {"updated", type datetime}}),

#"Expanded link" = Table.ExpandTableColumn(#"Changed Type", "link", {"Attribute:rel", "Attribute:title", "Attribute:href"},

{"link.Attribute:rel", "link.Attribute:title", "link.Attribute:href"}),

#"Expanded entry" = Table.ExpandTableColumn(#"Expanded link", "entry",

{"id", "title", "updated", "author", "link", "category", "content"}, {"entry.id", "entry.title", "entry.updated",

"entry.author", "entry.link", "entry.category", "entry.content"}),

#"Expanded entry.content" = Table.ExpandTableColumn(#"Expanded entry", "entry.content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "Attribute:type"}, {"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "entry.content.Attribute:type"}),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded entry.content",{"link.Attribute:title",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"link.Attribute:title"] = "Transactions")),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" =

Table.ExpandTableColumn(#"Filtered Rows", "entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", {"properties"}, {"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.pro"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata",{"link.Attribute:title", "entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.pro"}),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties" =

Table.ExpandTableColumn(#"Removed Other Columns1",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"},

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c..."}),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c..."

= Table.ExpandTableColumn(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c...",

{"JournalCode", "JournalDescription"}, {"JournalCode", "JournalDescription"}),

#"Sorted Rows" = Table.Sort(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.c...", Order.Ascending}})

in

#"Sorted Rows"

We get an error message 

Expression.Error: The column 'entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties' of the table wasn't found.
Details:
entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties

He could not find the 'entry content'. When we select the steps (rightside of the screen) he could not find the table.

This is strange because a few steps ealier we expand our table with 'entry content'.

The questions is to add the $select is that possible (incl. our special character)? What going wrong during the steps ?

[MarcelBeug]

If the $Select is not possible, then it would be visible in the step where you use it. I don't know if it would be possible or not,

With regard to the other issue, I would recommend to use normal step and column names, instead of names that include full URL's.

Otherwise when you get an error message, it is always good to mention at which step the error message appears for the first time to prevent us seeking for a needle in a haystack.

Possible you removed the column in step #"Removed Other Columns1"?

What do you mean with "When we select the steps (rightside of the screen) he could not find the table."?

Please provide a screenshot of the error message (after you renamed your steps and columns) where it first appears.

[a3]

The real problem is after the #remove other column step , in the #Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.  The system could not find the column 'entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties'.

The step #removed other column  is shown below.

Removing the column in step #"Removed Other Columns1" is not really a option.

The only difference with the other API's is that in the Transactions API the $select is added.

[a3]

What i found is that Power BI comes with a null value. As Sort citeria is JournalCode defined.

Expression.Error: 'Table.Sort' requires at least one sort criterion.
Details:
Table

[MarcelBeug]

In my opinion, the real problems is your step and column names with complete URL's included, making it almost impossible for humans to read your query.

In the step where you want to expand a column ending with "properties", there is no such column.

I can see a colum with a name ending with "pro".

With normal column names that would be obvious on first sight.