Forum Discussion
Strange behaviour when connection to a REST Api
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.:)
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.
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:
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