cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Get data from web with credentials

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).

 

6 REPLIES 6
Highlighted
Community Support
Community Support

hi, @Samrawit21 

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-...

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

@v-lili6-msft 

Thank you for your reply!

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

Highlighted

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
Highlighted

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!

Proud to be a Super User!
Paul on Linkedin.






Highlighted

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

Highlighted

@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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors