Strange behaviour when connection to a REST Api

[karekaasamoen]

I experienced some strange behaviour, fetching data from a REST Api resource. The resource is using cursor based pagination so I created to functions, GetPage and GetNumberOfPages to create a list of all necessary URL's. Then I created another function GetDetails, to get the full details from each order which needs to be fetched from another resource. 

The following snippet of code fetches the URL's, loop through theese pages to get all orderID's, and then fetch order details for all orderID's

let

    List = List.Generate( ()=>

        [i=0, ResList = GetPage(UrlSalesOrderLink)],

        each [i] < GetNumberOfPages(),

        each [i=[i]+1, ResList = GetPage([ResList][Next])],

        each [ResList][Data]),

    Convert=Table.FromList(List,Splitter.SplitByNothing()),

    Expand = Table.ExpandListColumn(Convert, "Column1"),

    Rename = Table.RenameColumns(Expand,{{"Column1", "Link"}}),

    Details = Table.AddColumn(Rename,"Details",each GetDetails([Link]))

in  Details

The code gives the following output

When I expand the details column, I will have this output

When the Type field has a value

[karekaasamoen]

Sorry, I'm new to this forum so I may created a post that was too long. What happens in the example above is that I receive an error message if i fetch 3 orderID's at the time. The error message telling me that it's impossible to convert 'RENTALORDER' to a type table. 

If I fetch 4 orderID's at the time (in the example there are 3 subsequent text fields) I will be able to Apply the query to the model. 

In the actual case I fetched 30 orderID's at the time instead of 10 - and i was able to work around this, but off course i wonder why this happens (and I also wonder why a empty field is loaded as a table insted of null value)

[Eric_Zhang]

@karekaasamoen

May I know what is the data source and how do you call the apis for each line? Kindly share the detailed approach so that I can try to reproduce the same behavior as yours.

[karekaasamoen]

Hi

The API isn't public available yet, so it will not be possible to reproduce outside of our development environment, but I'll post the details as well. The case is that I loop through a number of OrderID's to get detailed information about each order. The API uses cursor based pagination. To create the list I use the following code

List = List.Generate( ()=> 
[i=0, ResList = GetPage(UrlSalesOrderLink)],
each [i] < GetNumberOfPages(UrlSalesOrderLink),
each [i=[i]+1, ResList = GetPage([ResList][Next])],
each [ResList][Data]),

Convert=Table.FromList(List,Splitter.SplitByNothing()),
Expand = Table.ExpandListColumn(Convert, "Column1"),
Rename = Table.RenameColumns(Expand,{{"Column1", "Link"}}),

Using the functions GetPage() and GetNumberOfPages():

let
GetPage = (url as text) as record => let
Source = Xml.Tables(Web.Contents(url)),
DataTable=Source{0}[Table],
PagesTable=Source{1}[Table],
data = DataTable[SalesOrderLink],
next = "https://api.rambase.net" & Text.From(PagesTable{0}[NextPage]),
ResList = [Data=data, Next=next]
in ResList,

let 
NumberOfPages = (url as text) => let
Source = Xml.Tables(Web.Contents(url)),
PagesTable = Source{1}[Table],
Converted=Number.From(PagesTable{0}[Size]),
Pages=Number.RoundUp(Converted/30)
in Pages
in NumberOfPages

The resource I'm querying is this:

https://api.rambase.net/sales/orders?$select=salesorderlink&$top=30&$filter=status gt 1&$inlinecount=allpages&$db=TEM-NO&$access_token=P8ZhBhJc50aid23R4pZi7g2&$format=XML

From this list I use another function GetDetails(), in order to get details from each orderID in the list:

let
GetDetails = (url as text) => let
Details = try Xml.Tables(Web.Contents(url)) otherwise null
in Details
in GetDetails

In the main script the function is used this way:

Details = Table.AddColumn(Rename,"Details",each GetDetails([Link])),

The resource that is queried for details looks similar to this:

https://api.rambase.net/sales/orders/100007?$db=TEM-NO&$access_token=P8ZhBhJc50aid23R4pZi7g2&$format...

The details are added to the original table as a column, and when I try to expand this column (which of course is of type table), the field ORDERTYPE is sometimes empty - then it's loaded as a table (?), and somtimes it has a text value (for instance RENTAL) - then it's loaded a type text. 

Even if I solved the problem by query 30 ID's at a time instead of 10, i still got the error message (can't convert text RENTAL to table) in some transformation/modification operations. I solved it by converting all values to tables and then expand this table, using this function:

let
TransformColumn = (TableName as table, ColumnName as text) => let
TransformType = Table.TransformColumns(TableName,{ColumnName, each if _ is table then _ else Table.FromValue(_)})
in TransformType
in TransformColumn

So, this is about the amount of details I'm able to give you. The status is still that I was able to work around the problem, but wander why this happens.

Thanks for the reply by the way. Hopefully this is enough information for you to understand the case

[Eric_Zhang]

@karekaasamoen

Thanks sooooo much for that  soooooo detailed post, I've learnt a lot Power Query from it. 🙂

---

The details are added to the original table as a column, and when I try to expand this column (which of course is of type table), the field ORDERTYPE is sometimes empty - then it's loaded as a table (?), and somtimes it has a text value (for instance RENTAL) - then it's loaded a type text. 

---

So according to your description, that column is from Xml.Tables(Web.Contents(url)), have you checked that the API call always returns a valid/same formated xml? What does the API call return for the problematic records in your case?

[karekaasamoen]

I'll have a look into it. I can try to send you two xml-files, one from a problematic post and one from a non-problematic. I'll try to look into it through the day

[Eric_Zhang · Accepted solution]

@karekaasamoen

Thanks for your XML samples. 🙂 I've observed that strange behavior, the column is expanded as a table when the element in XML is blank otherwise as a normal string.

In my opinion it is really an abnormal behavior. I will report this internally and post back if there comes any update. One workaround so far I can think of.

#"Added Custom" = Table.AddColumn(#"Expanded Details1", "Type", each try Text.From([Details.Type]) otherwise "")

Thanks again for your feedback.:)