Scheduled refresh Post call with Web.Contents doesn't work

[Anonymous]

Hi everyone,

-- See EDIT at the end of the post. Maybe this has to do with a 303 response?

The community has helped me alot lately. Hopefully someone has a solution/answer/explanation for the following problem.

I'm retrieving data from a server by means of a post call with Web.Contents. This works perfectly fine in power BI desktop, no issues at all.

However, when I publish the report to the report server and try to refresh the data an error occurs: "Unable to connect to the remote server".

 

This is my M power query (works fine in Power BI desktop):

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"

As you can see, I tried a solution with RelativePath as suggested by some other users. However, the report server still can't manage to establish a connection.

Thank you for help!

EDIT:

Maybe this could be part of the problem? When I want to retrieve data from the server a url like: foo.bar.com/export/download is used. In my solution i used url = "foo.bar.com" and RelativePath = "/export/download".

However when I reach foo.bar.com the address is redirected to foo.bar.com/@@login?camefrom=%2F%40%40index (see screenshot below). Maybe Power BI can't evaluate foo.bar.com in a static way because it should validate foo.bar.com/@@login?camefrom=%2F%40%40index? However, when retrieving data the "@@login?camefrom=%2F%40%40index" should not be used. How can I trick Power BI to do so?

[parry2k]

@Anonymous does it work in the desktop after you use the relative path?

[Anonymous]

@parry2k Yes, it works fine using the relative path in the desktop.

[v-easonf-msft]

Hi, @Anonymous 

Can you tell me if your problem is solved?

If you still need help, please share more information about it.

Best Regards,
Community Support Team _ Eason

[Anonymous]

 Hi @v-easonf-msft ,

Thank you for your answers!

Unfortunately, it still doesn't work. The report server can't establish a connection to 

"foo.bar.com" (with RelativePath = "/export/download") because foo.bar.com seems to redirect to foo.bar.com/@@login?camefrom=%2F%40%40index. The connection can be established with "foo.bar.com/@@login?camefrom=%2F%40%40index, but in combination with the RelativePath = "/export/download" part the post call doesn't work, because foo.bar.com/export/download is required. The static evaluation in the report server is being a tough problem here. It all works perfectly fine in desktop...

[v-easonf-msft]

Hi， @Anonymous 

After reviewing some documents, I suspect that there may be something  wrong with your data connection string.

Add and Verify a Data Connection (Report Builder and SSRS) 

Create data connection strings - Report Builder & SSRS 

Specify Credential and Connection Information for Report Data Sources 

Best Regards,
Community Support Team _ Eason

[parry2k]

@Anonymous check this post Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI Chris Webb's BI Blog (crossjoin.co.uk)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k , thank you for your answer!

I recently looked at that post. I tried using the relative path trick. It should evaluate the base url correctly but unfortunately that is not the case. Can't understand why the query cannot be evaluated correctly.