Scheduled refresh Web.Contents (Post call) doesn't work

[Anonymous]

Hi everyone!

I'm retrieving data from a webserver with the help of the Web.Contents function. 

This works perfectly in Desktop. However, a connection cannot be established when the report is published to the report server ("Unable to connect to remote server").

This first has to do with the fact that Power BI does some static analysis on the code to determine what the data sources for the dataset are and whether the supplied credentials are correct, as stated by this great post: Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI Chris Webb'... . Therefore, i added the suggested solution with RelativePath but this doesn't seem te work either (it still works in Desktop though).

The login to webserver is redirected from foo.bar.com to foo.bar.com/@@login?camefrom=%2F%40%40index. The url for the HTTP post call however is foo.bar.com/export/download. So this means that Power BI should evaluate "foo.bar.com/@@login?camefrom=%2F%40%40index" in a static way. But when executing the code in Web.Contents: url = "foo.bar.com" and RelativePath = "/export/download" should be used. You see the problem? How can I get this working?

So the question is: How can I trick Power BI into evaluating "foo.bar.com/@@login?camefrom=%2F%40%40index" but executing Web.Contents with 

Web.Contents(foo.bar.com, [RelativePath="export/download"...etc etc...)

Thank you in advance!

let
  url = "[aWebsite]",
  auth_key = "Basic [key]",
  header = [#"Authorization"=auth_key, #"Content-Type"="application/x-www-form-urlencoded"],
  body = "login=XXXXX&password=XXXXX&mpoint=-1&start_date=2021-11-01&end_date=&methode=nochoice&preparation_category=-1&prepared_by=-1&product_description=",
  Source =  Csv.Document(Web.Contents(url, [RelativePath="export/download",Headers=header,Content=Text.ToBinary(body)]),[Delimiter=";"]),
  #"PromoteHeaders" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  #"Change type" = Table.TransformColumnTypes(#"PromoteHeaders",{{"Werk-/achtergrondruimte", type text}, {"methode", type text}, {"Nummer monster", type text}, {"Datum bereiding", type date}, {"Bereider/Aantal mdw.", type text}, {"Productomschrijving", type text}, {"Categorie bereiding", type text}, {"chargenummer", type text}, {"Tijd open (min.)", Int64.Type}, {"Waarde 3 dg", type text}, {"Waarde 7 dg", Int64.Type}, {"Laatst aangepast door", type text}, {"Micro-organisme", type text}, {"Soort materiaal gedesinfecteerd", type text}, {"Opmerking", type text}})

in 
 #"Change type"

[lbendlin · Accepted solution]

As Chris Webb has stated the only way to do this at the moment is to write a custom Power Query connector.  That will allow you to implement the negotiation/redirect phase correctly.