Linking authentication-needing files

[UsePowerBI]

Hello!

I have a domain/website that needs a form authentication. After I authenticate, I can click on some links which will download some xls files.

I want to put these links into a Data Connection and download them directly in Excel. However, when I do that, it downloads the login page instead of the actual file.

I tried various authentication and privacy settings but non worked.

Is it possible to have Excel use the cookie from MS Edge or somehow use the authentication I did via MS Edge?

Thanks!

[UsePowerBI]

@Ehren  thanks indeed I read about the Legacy importer and tried it but not useful.

I guess what I am struggling to understand is that if Web.Contents shares cookies with IE and if I have an active IE session where the IE is not closed, why isn't the session kept alive so that Web.Contents will 'share' this session?

I mean, whenever Web.Contents shares IE cookies, it initiates a new session every time so it will only work when the cookies are persistent?

[Ehren]

PQ in Excel will use saved Internet Explorer (not Edge) cookies if you're using Web.Page to download the data. However, there's no way to script page clicks or other kinds of automation.

[UsePowerBI]

@Ehren  thanks but can you tell me please how to set up such connection?

Normally I go via IE to https://some.domain.com and I login via a form and then there is another form for authentication. At the end, I manage to authenticate and initiate a session.

Then, if I visit https://some.domain.com/donwload/943/ an xlsx will be downloaded.

How do I create a connection to such xlsx file?

I add a Get Data > From Web connection and insert the https://some.domain.com/donwload/943/

Then, it asks me for authentication type, which should I choose and at which level (i.e. domain.com or domain.com/donwload etc)?

Thanks!

[Ehren]

Try using Anonymous auth for domain.com.

[UsePowerBI]

@Ehren  thanks, I did try it and it does not work. It downloads this and it shows the login webpage:

I can check the headers when I visit the domain.com/download/425 link, would that help?

[v-kelly-msft]

Hi  @UsePowerBI ,

Did you try to click edit or transform at the right bottom corner of the pic showed below:

When you go into  query editor,you could expand the tables to get the contents.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Ehren]

Does the website maintain the login across sessions? In other words, if you login in IE and are able to download the xls files, and then close the browser, are you able to download the xls files without logging in the next time you open IE?

[UsePowerBI]

@Ehren  indeed the website does not maintain the login session if I close the browser

What can be done in this situation?

I can keep IE/Edge open after I login manually, if needed!

[Ehren]

I'm not sure there's anything that can be done in this case.

Does the website support any kind of non-interactive login/auth? That's probably the only way to make it work, if the cookies don't persist across browser sessions.

[UsePowerBI]

@Ehren thanks but cannot I make Power Query 'grab'/use the cookies from the browser's session? Can I enter the cookie details manually or can I specify the cookie folder of my browser so that Power Query (or any other way e.g. PowerShell) can pick them up?

[Ehren]

Web.Contents shares cookies with Internet Explorer (but not with Edge). But beyond that there's no way to share cookies, and if the site you're accessing doesn't persist logins across browser sessions, there's no way to make this work in PQ.

EDIT: One other thing you could try would be to use the legacy Web data source in Excel, which pops an interactive browser. If you sign in via that and then try refreshing in PQ, that may work.