Connecting to an Excel in https://wcs.bsnconnect.com/sites/

[ValeriaBreve]

Hello!

This is probably a pretty basic question...

I am trying to connect to an Excel file on an address of the type "https://wcs.bsnconnect.com/sites/" which I do not know what it actually corresponds to.

I tried with "Excel Workbook", "Web", but it is not working.

I am getting the error message "DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server"

I am able to open the Excel file under my userID from the same link, so I guess the credentials are not an issue?

Thanks for your help!

Kind regards

Valeria

[ValeriaBreve]

Hello, thanks!

In the meantime what I did was to try and access the URL from the browser without the Excel file in the path - and I did not have access. So I asked for it... I am not sure if I was given it in the meantime (I have no notifications), but all of a sudden the Web/Excel connection started working. Still, I am interested to learn more about how you would debug this. I have gotten the response from the server and it came as 800 lines of text. What do I have to look for? I am hesitant to copy and paste it here because I am not good enough to understand the confidential information that might be inside it....

[ams1]

Happy it started working.

If you would have used the snippet I gave you before - when you didn't have access -, probably you would have seen what you saw in your browser -> a HTML page saying forbidden, access denied or something in the sorts of.

In the absence of curl, Postman etc., that query is IMO a solid base for further investigations -> you will see there in plain text: json, HTML etc.

[ams1 · Accepted solution]

Hi,

If you can open that file via web browser it doesn't mean you can open it as easily using PowerQuery as the browser additionally runs the javascript that might have redirects etc.

Postman or curl or the snippet below should reveal the actual server response - which you mention it's a HTML - and starting from that HTML response we'll see where to go next and if it's easy.

let
 serverResponseAsText = 
    Text.FromBinary( 
        Web.Contents(
            "put-web-address-here" 
        )
    )
in
    serverResponseAsText

IF you decide to paste the serverResponseAsText here, PLEASE remove confidential information.