none
Power Query executing web.Contents query twice - How to stop? RRS feed

  • Question

  • I have a (SOAP) web service call being successfully made by a web.Contents PQ query.  THe only problem is that it does it twice, every time.

    Here is the guts of the query:

    let
      xmlREQ = {I build the string}

     Source = xml.Document(Web.Contents("//theurl", [Content=Text.ToBinary(xmlReq), Headers=[SOAPAction = "actionSpec"]])),

    Value=Source{0}[Value],

    Value1=Value{0}[Value],

    Value2 = Value1{0}[Value]

    in

    Value2

    ---------

    I used Fiddler to verify that the web POST request is truly being sent twice.  Don't know much about PQ or M, but suspect that something in the above M script is triggering a double query. PQ doesn't automatically send twice for any reason, does it?  Appreciate any help!

    Thanks,

    Rand

    


    Rand Wrobel, Consultant

    Tuesday, July 21, 2015 1:20 AM

Answers

  • We emphatically state that Power Query should not be used to update data in this fashion because we make zero guarantees around executing only one time. Power Query is currently intended for read-only scenarios.

    Wednesday, July 22, 2015 2:15 AM

All replies

  • Hi Rand,

    This forum is for developers discussing develop issues about Excel automation and customization. Since the issue is more relative to the Power Query, I would like to move it to Power Query forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 22, 2015 1:37 AM
  • We emphatically state that Power Query should not be used to update data in this fashion because we make zero guarantees around executing only one time. Power Query is currently intended for read-only scenarios.

    Wednesday, July 22, 2015 2:15 AM
  • Ok,

    Well, I didn't say anything in my post about "updating data", though the supported Content parameter to make POST calls could indicate that.

    It seems the problem is Web.Contents itself. Putting a Web.Contents call in (to check for an immediately prior run) as a formula or inline won't help, because every Web.Contents step apparently gets executed twice.

    Even accepting "PQ is currently intended for read-only scenarios", this is unfortunate and deserves to be fixed for a few reasons:

      A) because long-running/expensive queries will get run twice, doubling the execution time,

      B) it adds load to potentially heavily loaded resources

      C) it distorts usage demand on sources, skewing reports, and some third-party source are sensitive to volume, disabling users with higher hit rates

    These are magnified as PQ is applied to larger, Big Data scales.  It seems likely to me that a number of your users with the largest datasets are putting up with this unfortunate double-execution and would be very happy to see it corrected. This will only get worse as PQ is integrated with base Excel 2016.

    The behavior to (at times?) automatically run the query when finished editing is also problematic, because of these. A switch between current behavior and to make such query runs and Web.Content calls singular only upon an explicit (connection?) trigger would solve these problems.

    Given PQ's "currently intended" purpose, does Microsoft have any other Office-based non-C#-code approach to make (cross-domain) SOAP calls?

    Please put this on your "to-be-fixed" list.

    Thanks,
    Rand


    Rand Wrobel, Consultant

    Monday, August 17, 2015 10:43 PM
  • I'm also having the same issue and would like to see it correct ASAP. Having two calls for each export is going to create problems. 
    Tuesday, January 3, 2017 5:31 PM
  • I agree with @RandWro

    Many legacy web services use the body of an HTTP POST to pass authentication & query parameters, nothing to do with updating data.

    Thus, when power query sends multiple HTTP POST queries, the web service treats the second as a duplicate and responds with differently (which of course is implementation dependent)

    Thanks,

    Saturday, April 13, 2019 10:03 PM