Hi Mohinder,

When you enter the web URL the next dialog box will ask you how you want to connect to the page and whether you need to enter any credentials.

Mynda

Hi Mynda

Not sure What I am missing. When I click on OK I do not get an option to connect.

Please see the attached.

Thank you

Mohinder

Hi Mohinder,

You only get this dialog box the very first time you connect to the web page and after that it remembers your choice. You needed to select 'Basic' and then you can enter your credentials. See images attached.

However, you can edit the settings via the Power Query editor File tab > Options & Settings > Data Source Settings > Edit Permissions > Edit Credentials.

Mynda

Hi Mynda

Thank you I am able to get the Credentials dialog box. However even through the credentials are correct,  I am unable to log in. Have I missed some thing?

Mohinder

Hi Mohinder,

Looks like there are some problems with authentication at the moment. More is explained here along with some workarounds.

Mynda

Just for information See attached. Worked for me.

Mo

Hi,

Depends on the way authentication is designed on each site.

Some sites require simple authentication with username and pass provided in binary format in the request content, but they provide a temporary access token.

Source = Json.Document(Web.Contents(URLString, [Headers=[#"Content-Type"="application/x-www-form-urlencoded",
                                                                           Accept="application/json"],
                                                                           Content=Text.ToBinary("username=" & User & "&password=" & Pass)])),
token = Source[token]

With this token, data can be extracted, but the token needs to be passed to a custom header:

Source = Xml.Tables(Web.Contents(URLString, [Headers=[#"X-Auth-Token"=Token]])),

If the website requires, Power Query can encode user and pass:

Token = Text.From(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[API Token]),
WID=Text.From(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Workspace ID]),
Credentials=Binary.ToText(Text.ToBinary(Token & ":api_token"), BinaryEncoding.Base64),
Source = Json.Document(Web.Contents("https://toggl.com/reports/api/v2/details?workspace_id="& WID &"&since="& FromDate &"&until="& ToDate &"&user_agent=api_test",[Headers=[#"Authorization"="Basic " & Credentials]])),

Some sites require a bearer token:

JsonData = Json.Document(Web.Contents("https://api.onedrive.com/v1.0/drive/root:/" & Uri.EscapeDataString(FolderPath) & ":/children", [Headers=[#"Accept" = "application/json", #"Authorization" = "bearer " & Token]]),1252),

In conclusion, each site documentation must be studied, they usually provide all authentication details to access their data.

Thank you Catalin.

Great -detailed answer.

Mo