Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I'm building a Custom Connector to pull data from one of our products using a Web API. It calls an XML Web Service which returns an XML response.
The overall Connector works perfectly, but I need to refine the call. It needs to loop through the Web Call until all rows have been returned as the Web Service will only return 5000 rows per response.
When executing the Web Call without Paging (getting max 5000 rows), it works perfectly. There is a lot of manipulation to get the XML response into the correct format (the response is horrendously formatted, but there is zero I can do about it).
As soon as I wrap this call up in the Pagination logic, when connecting in Power BI, it's just returning 'This table is empty'.
I've checked every component and it works fine. We also have a JSON format of the Web Service which works perfectly including wih the Pagination, but I can't use that because it's missing key data in the response.
This is the code in the connector (the code is too long to add as code here):
The GridWSCall part on it's own works perfectly and I get the data in Advance Editor and Power BI correctly.
I'm out of ideas on why I'm getting no data in Power BI:
Solved! Go to Solution.
I've resolved the issue but created a new problem.
The incoming repsonse format for JSON is different to the XML one, so I had to re-format the XML response slightly into the same format. Now the pagination is working, but leads to my new problem.
As its paginating, firstly it seems to be making the WebService call twice from the outgoing messages I can see in Fiddler. I can see the same request twice.
Secondly, it appears to be re-calling messages it has already requested and in random orders:
eg
Call 1 > Cursor Position 1
Call 2 > Cursor Position 1
Call 3 > Cursor Position 2001
Call 4 > Cursor Position 2001
Call 5 > Cursor Position 4001
Call 6 > Cursor Position 4001
Call 7 > Cursor Position 2001
it eventually loaded all the data, but seems to have done a lot more work than it should have, thereby taking longer. My test table only had 16K rows which it grabbed in 2000 row increments, but we have tables with 1m+ records.
Any ideas?
I've tested it by removing all of the manipulation of the data and just calling the WebService within the Pagination logic. I hardcoded the known responses (number of rows, IsMore) and it's still returning 'This table is empty'.
XMLWebService = (hostUrl as text, gridCode as text, optional filterField as text, optional RangeStart as datetime, optional RangeEnd as datetime) =>
let
hostUrl = hostUrl,
gridCodeString = """" & gridCode & """",
filterFieldString = """" & filterField & """",
filterFrom = if RangeStart = null then """1900-01-01""" else """" & RangeStart & """",
filterTo = if RangeEnd = null then """""" else """" & RangeEnd & """",
XMLUserName = Extension.CurrentCredential()[Username],
XMLUserPassword = Extension.CurrentCredential()[Password],
Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
each [IsMore] <> false,// Whilst this is true, keep going
each [GridWSCall = Xml.Tables(Web.Contents(hostUrl ,
[Headers = [#"Content-Type" = "text/plain"],
Content = Text.ToBinary(Text.Combine({ "<?xml version=""1.0"" encoding=""utf-8""?>",
"<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"">
<soapenv:Header xmlns:wsa=""http://www.w3.org/2005/08/addressing"">
<wsse:Security xmlns:wsse=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"" soapenv:mustUnderstand=""1"">
<wsse:UsernameToken xmlns:wsu=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"" wsu:Id=""UsernameToken-1"">",
"<wsse:Username>",XMLUserName ,"</wsse:Username>",
"<wsse:Password Type=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText"">",XMLUserPassword,"</wsse:Password>",
"</wsse:UsernameToken>",
"</wsse:Security>",
"<wsa:To>", hostUrl , "</wsa:To>",
"<wsa:MessageID>urn:uuid:20210225001</wsa:MessageID>",
"<wsa:Action>urn:processMessage</wsa:Action>",
"</soapenv:Header>
<soapenv:Body>
<ABC123_GetDataOnly_001 xmlns=""http://schemas.datastream.net/MP_functions/ABC123_GetDataOnly_001"" xmlns:xs=""http://www.w3.org/2001/XMLSchema"">
<FUNCTION_REQUEST_INFO REQUEST_TYPE=""LIST.HEAD_DATA.STORED"">",
"<GRID GRID_NAME=", gridCodeString
, " USER_FUNCTION_NAME=" ,gridCodeString
, " NUMBER_OF_ROWS_FIRST_RETURNED=", """2000"""
, " CURSOR_POSITION=", """1"""
, " TERSERESPONSE=""true""", " RESULT_IN_SAXORDER=""true"""
, " LOCALIZE_RESULT=""false""/>",
"<GRID_TYPE TYPE=""LIST""/>",
"<MULTIADDON_FILTERS>",
"</MULTIADDON_FILTERS>",
"<DATASPY DATASPY_ID=""""/>",
"</FUNCTION_REQUEST_INFO>",
"</ABC123_GetDataOnly_001>",
" </soapenv:Body>",
" </soapenv:Envelope>" } ))])),// retrieve results per call
Last_Key = 12,
IsMore = if 13 < 1 then null else false,
Counter = 13+1,
splitList = GridWSCall
]
,each [splitList] // selector
) ,1)
, Custom = Table.Combine(Pagination)
in Custom ;
Hi @Anonymous
I think the problem may come from the start and end part of Pagination codes. Maybe the loop logic has some mistake. Try using different start values and some definite loop times (e.g. once, twice) to test it. And also test whether the end loop is correct.
Regards,
Community Support Team _ Jing
I've resolved the issue but created a new problem.
The incoming repsonse format for JSON is different to the XML one, so I had to re-format the XML response slightly into the same format. Now the pagination is working, but leads to my new problem.
As its paginating, firstly it seems to be making the WebService call twice from the outgoing messages I can see in Fiddler. I can see the same request twice.
Secondly, it appears to be re-calling messages it has already requested and in random orders:
eg
Call 1 > Cursor Position 1
Call 2 > Cursor Position 1
Call 3 > Cursor Position 2001
Call 4 > Cursor Position 2001
Call 5 > Cursor Position 4001
Call 6 > Cursor Position 4001
Call 7 > Cursor Position 2001
it eventually loaded all the data, but seems to have done a lot more work than it should have, thereby taking longer. My test table only had 16K rows which it grabbed in 2000 row increments, but we have tables with 1m+ records.
Any ideas?
Hi @Anonymous
It's difficult to find the cause at my side... Is it possible to grab all the requests sent to the API and check the generated parameter values? And do the JSON and XML reponses always return the same number of rows?
Jing
Thanks. From what I have read, it sounds like it is just the behaviour of the Web Service when the Authorisation credentials are part of the body, which is the case for the XML Web Service i'm using. Our JSON one, the Authorisation is in the header and works perfectly and much more efficiently. I guess this is just the way it will be.
Thanks Jing. This connector will either process the message as JSON or XML, depending on the connection parameter.
The JSON call is absolutley identical to the XML call (copy and paste) with only the the actual Web call part changing. However, the JSON with pagination works absolutley perfectly.
The two messages are identical apart from the GridWSCall, so I'm really confused as to why JSON works and XML doesnt.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.