I was trying to do the same on a website with credentials to no avail. I eventually gave up trying with Power BI. I succeeded using Excel and VBA (I haven't a clue about VBA, but Google is your friend for these things...).
It is a real pain, since it involves manually updating the excel file, but...
I hope there is a simple way to do this in PBI.
Did I answer your question? Mark my post as a solution! In doing so, you are also helping me. Thank you!
I believe I had to activate the option of forms in Excel, but I can't remember how right now.
As this was something I used to do on a daily basis, I also set up a Macro to execute the data extraction automatically on opening the file and then saving the file once done. I also set up my Windows to open the file as pat of the start process, so the whole process would take place every time I started my PC. This way Power BI will pick up the latest version on refresh.
Not very efficient I'm afraid, but it worked for my particular needs.
The (relevant) code I used is this (as I say I know very little about VBA so won't be of much help if the macro doesn't work properly for you). Most of this comes from the website I quoted above:
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject
'create a new instance of ie
Set ieApp = New InternetExplorer
'you don’t need this, but it’s good for debugging
ieApp.Visible = True
'assume we’re not logged in and just go directly to the login page