cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmercurio
Frequent Visitor

Query works in Advance Editor but in a Custom Connector it's returning "This table is empty"

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):

Screenshot 2021-07-20 214727_1.png

Screenshot 2021-07-20 214755_2.png

Screenshot 2021-07-20 214851_3.png

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:

Screenshot 2021-07-20 214225.png

1 ACCEPTED 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?

 

View solution in original post

6 REPLIES 6
mmercurio
Frequent Visitor

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 ;
v-jingzhang
Community Support
Community Support

Hi @mmercurio 

 

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?

 

View solution in original post

Hi @mmercurio 

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors