Get data from web with credentials

[Anonymous]

I want to extract data from the web, which requires an authentication credential and when I try to write the credentials, It doesn't allow me (it's not active to write a credential).

[Juergen_PowerBI]

Hi Everyone,
not sure if this thread is still valid by I struggled with the same issue of trying to access via a Web Query and was not able to enter my credentials to authenticate. Stuck on this end as entering my credentials at the "basis" form did not work and I got the same error message from the Desktop app: "Web.Browsercontents only supports anonymous login". 

Any help would be much appreciated. 

Regards,

[v-lili6-msft]

hi, @Anonymous 

First, go to Edit queries->data source setting, clear or edit this data source credential, select "Basic" from the window and type your username and password.

Then selecting the Extract table using examples presents an interactive window where you can preview the content of the Web page, and enter sample values of the data you would like to extract.

https://docs.microsoft.com/en-us/power-bi/desktop-connect-to-web-by-example#using-get-data-from-web-by-example

Best Regards,
Lin

[Usman]

I'm getting this error after doing what you said.

---

DataSource.Error: Web.BrowserContents currently supports only anonymous credentials.
Details:
    DataSourceKind=Web
    DataSourcePath=https://simplywall.st/login

[PaulDBrown]

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.

[thakks]

I stumble to the same error message, can you poing me to the solution you referring to through Excel and VBA by any chance @PaulDBrown 

Web.BroserContents currently supports only anonymous credentials.

Thanks

[PaulDBrown]

@thakks 

As I stated, I know next to nothing about VBA and Excel Macros, but I (laborously) managed to come up with a working solution.
I followed a couple of online tutorials, one of which is here: 

http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/

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:

Sub ImportData()

'Sub GetTable()

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

ieApp.Navigate "https://www.YOURWEBSITELOGINPAGE.COM"

Do While ieApp.Busy: DoEvents: Loop

Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names

With ieDoc.forms(0)

.UserName.Value = "NAME SURNAME"

.Password.Value = "PASSWORD"

.submit

End With

Do While ieApp.Busy: DoEvents: Loop

Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'now that we’re in, go to the page we want

ieApp.Navigate "https://www.YOURWEBSITEPAGEWITHDATA.com"

Do While ieApp.Busy: DoEvents: Loop

Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'get the table based on the table’s id

Set ieDoc = ieApp.Document

Set ieTable = ieDoc.all.Item("TABLENAME")

'

' ImportData Macro

' Connect to TABLE website: this code is for my particular case

'

'

   Range("A2:N110").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;https://www.YOURWEBSITEPAGEWITHTABLE.com", Destination:= _

        Range("A1"))

        .Name = "NAMEOFPAGE"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .WebSelectionType = xlAllTables

        .WebFormatting = xlWebFormattingNone

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

‘this step is to paste into a selection range in Excel

Range("A2:N150").Select

    'close 'er up

ieApp.Quit

Set ieApp = Nothing

End Sub

[Anonymous]

Hello PBI community

I keep getting this same error in PowerBI on the one hand, and in Excel on the other hand. I would prefer to avoid Excel and VBA as I need the feature 'Add table by example' that is only available in PBI to be able to extract the data I need. Is this still unsolved in PowerBI?

Thank you for your help.

See parallel thread on this topic: 

https://community.powerbi.com/t5/Power-Query/Not-able-to-import-data-from-webapge-with-valid-credent...

[Anonymous]

@v-lili6-msft 

Thank you for your reply!

I keep getting an error message " The credentials provided are invalid "