Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aldodfm
Frequent Visitor

Anonymous access

Hello @Community,

 

I have a power query with a REST api call:

 

 authKey = "Bearer blablablaKey",
 url = "https://blablablaendpoint.com/api/v1/usage/2016",

 GetJsonQuery = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"]
     ]
 ),
FormatAsJsonQuery = Json.Document(GetJsonQuery),

So, this power query use "Anonymous" authentication. When i refresh this query in Power BI Desktop appears credentials error, but if you accept the power query is executed and everything is ok.

But when i try to schedule refresh in Power BI service appears the credentials problems but doesn't exists a way to say "execute anyway".

 

Is like Power BI service checks the anonymous access to the URL and if it doesn't work, power bi doesn't try to execute the power query script.

 

power_bi_erro_01.pngpower_bi_erro_02.pngpower_bi_erro_03.png

 

Please, there is something to do? Power BI team will do something?

 

Thx in advance.

1 ACCEPTED SOLUTION

I had the same issue and logged a support ticket with Microsoft because I wanted to build my own Google Analytics queries (include filters, segments, etc).

Here's the answer I got from the support team:
This is not possible due to constraints with the way Web.Contents stores credentials. They are stored based upon the URL value passed, and there can only be one URL per Dataset. In this case, you're trying to short-circuit a typical oAuth2 flow (one URL) and call the API (another URL). I made an attempt at using RelativePath to 'trick' the service, but stills require that the root of those two endpoints returned a valid HTTP 200 response to indicate connection success. In this case, https://www.googleapis.com returns a 404. Unfortunately, therefore it is not possible as an uploaded PBIX file. I suggest creating a new issue describing what data you would want to see in the Google Analytics content pack (https://app.powerbi.com/groups/me/getdata/services/google-analytics) and we can see about adding it to the model.

The only workaround for me so far, is to execute the query elsewhere (for me it's Google Spreadsheet) and connect PBI to that as a data source.

 

View solution in original post

33 REPLIES 33
apods1979
Helper I
Helper I

Good afternoon!!!
I saw many websites, chats and posts, but few are objective and each person values his method as if it were unique !!!
Each has its logic, so there is no single method !!
Objectively, for me ...
It worked this way:

Enjoy: 😘

===========================
let
tenantId = "12345678-9012-3456-7890-123456789012",
clientId = "abcdefgh-9012-3456-7890-123456789012",
clientSecret = "1234567890qwertyuiopasdf",
grantType = "client_credentials",
resource = "https://graph.windows.net/",
endpointUsers = "/users",
apiVersion = "?api-version=1.6",

baseURL="https://login.microsoftonline.com",
relativePath = "/"&tenantId&"/oauth2/token",
urlToken = baseURL & relativePath,

body = [client_id=clientId,
grant_type=grantType,
client_secret=clientSecret,
resource=resource],

//Access
Source = Json.Document(Web.Contents(urlToken, [Headers=[#"Accept" = "application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],Content = Text.ToBinary(Uri.BuildQueryString(body))])),
accessToken = Source[access_token],
tokenType = Source[token_type],
fullAccessToken = tokenType&" "&accessToken,

//Get Users
SourceUsers = OData.Feed(resource & tenantId & endpointUsers, [Authorization=fullAccessToken], [Implementation="2.0", Query=[#"api-version"="1.6"]]),

Step1 = Table.RemoveColumns(SourceUsers,{"createdObjects", "department", "employeeId", "facsimileTelephoneNumber", "legalAgeGroupClassification", "jobTitle", "telephoneNumber", "mobile", "givenName", "physicalDeliveryOfficeName", "consentProvidedForMinor", "sipProxyAddress", "streetAddress", "city", "state", "country", "postalCode", "ageGroup", "companyName", "preferredLanguage", "manager", "directReports", "members", "transitiveMembers", "owners", "ownedObjects", "appRoleAssignments", "licenseDetails", "oauth2PermissionGrants", "ownedDevices", "registeredDevices", "assignedLicenses", "assignedPlans", "deletionTimestamp", "immutableId", "isCompromised", "lastDirSyncTime", "passwordProfile", "provisionedPlans", "provisioningErrors", "proxyAddresses", "signInNames", "usageLocation", "userIdentities", "memberOf", "transitiveMemberOf", "thumbnailPhoto", "createdOnBehalfOf", "dirSyncEnabled", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "showInAddressList"}),
Step2 = Table.TransformColumnTypes(Step1,{{"createdDateTime", type datetime},{"refreshTokensValidFromDateTime", type datetime}}),
Step3 = Table.TransformColumns(Step2, {"otherMails", each Text.Combine(List.Transform(_, Text.From)), type text})
in
Step3

jeffshieldsdev
Solution Sage
Solution Sage

I just thought I'd add my work-around to this thread...it's not ideal, but work-arounds never are 🙂

 

Because I'd prefer not to hard-code my API credentials within the PBIX file, I put them in a plain text file, and upload it to OneDrive.  I then connect to the text file from PBI to pull in the credentials.  In my case, I'm connecting to the BridgeEdge API.

 

Parameters:

  • BrightEdge Credentials File - URL to the OneDrive file (eg. "http://.../MyCredentials.txt") containing the credentials, in the format "username:password"
  • BrightEdge Account - required by API--an ID of the account to query data for
  • BrightEdge Query - required by API--the BQL query (text that needs to be POSTed in the BODY)

Queries:

  • BrightEdgeData 
let
    URL = "https://api.brightedge.com",
    Body = #"BrightEdge Query",
    Options = [
        Headers = [
            Authorization = "Basic " & Binary.ToText(Web.Contents(#"BrightEdge Credentials File"))
        ],
        RelativePath = "/3.0/query/" & #"BrightEdge Account",
        Content = Text.ToBinary(Body),
        Timeout = #duration(0,0,5,0)
    ],        
    Source = Web.Contents(URL, Options),
    #"Imported JSON" = Json.Document(Source,65001),
    values = #"Imported JSON"[values],
    #"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"domain", "rank_p1"}, {"domain", "rank_p1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"domain", type text}, {"rank_p1", Int64.Type}})
in
    #"Changed Type"

Hopefully this technique is helpful to someone else 🙂

@jeffshieldsdev - thanks for the good idea about keeping credentials and the body out of PowerBI.

One question though - when you load these external file into PowerBI, how do you get them read as Text? 

 

For example, my 'body' is in Json format (multiple rows), but saved as a .txt. It reads in as a Table and I can convert to List. However when i try to use it in the main query, I get thrown an error.

 

Expression.Error: We cannot convert a value of type Table to type Text

@jeffshieldsdev - All good. Solved my question.

 

Load .txt file in a seperate query only using: 

= File.Contents("C:\Users\.... 

 

Then in the main query, reference this via:

body = Text.FromBinary(myFile,1252),

 

Great idea, thank you. 

DuncanP
Advocate II
Advocate II

Hi

 

I have this exact same issue.

 

The "authentication" is baked into the Power Query code itself through the API key, so setting the authentication method to anonymous works perfectly in Power BI desktop. However I get the same invalid credentials errors when trying to refresh from Power BI.

 

My code is similar:

Source = Json.Document(Web.Contents("https://myapi.com/api/method", [Headers=[#"X-API-Key"="Some_GUID"]])),

 

If anyone can come up with a workaround for this, I'd be very grateful as well!

 

Duncan

 

EDIT:

Just to note, anonymous authentication works fine at the root level of the api (https://myapi.com/api) and simply lists the methods. However in the Power BI refresh, it tries to load the method url which of course fails without the api key.

I had the same issue and logged a support ticket with Microsoft because I wanted to build my own Google Analytics queries (include filters, segments, etc).

Here's the answer I got from the support team:
This is not possible due to constraints with the way Web.Contents stores credentials. They are stored based upon the URL value passed, and there can only be one URL per Dataset. In this case, you're trying to short-circuit a typical oAuth2 flow (one URL) and call the API (another URL). I made an attempt at using RelativePath to 'trick' the service, but stills require that the root of those two endpoints returned a valid HTTP 200 response to indicate connection success. In this case, https://www.googleapis.com returns a 404. Unfortunately, therefore it is not possible as an uploaded PBIX file. I suggest creating a new issue describing what data you would want to see in the Google Analytics content pack (https://app.powerbi.com/groups/me/getdata/services/google-analytics) and we can see about adding it to the model.

The only workaround for me so far, is to execute the query elsewhere (for me it's Google Spreadsheet) and connect PBI to that as a data source.

 

Anonymous
Not applicable

Hey, Im having the same issue right now.

My base URL (https://api.applicationinsights.io/v1/apps) returns 404 so I need to find workaround.

Do you think that I could use Google spreadhsheet as a workaround?

 

Thanks,

Rafal

Hi fso,

 

Im having the same issue that I can only refresh my data in the desktop version but not online. 

Coule you please elaborate a little bit more when you say that you use google sheets to execute the query?

 

Thanks!

Well that's actually fixed it for me, so thanks!

 

I've modified my json call to:

Source = Json.Document(Web.Contents("https://myapi.com", [Headers=[#"X-API-Key"="Some_GUID"], 
RelativePath="/api/​method"])),

which now allows it to authenticate properly. In this case I'm lucky that the root url of the api works with anonymous access.

 

 

Duncan

Anonymous
Not applicable

5 years later this is still useful. Thank you so much 🙂

Hello @DuncanP, I am facing the similar issue. I have done all the workarounds that you suggested. but still the issue is persisted.

 

let 
AuthKey = "Basic mykey",
url="https://tfs.mnsu.edu/tfs/ITS%20AppDev/Test%20Agile%20Project/_apis/wit/wiql?api-version=1.0",
body="{
  ""query"": ""Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.WorkItemType] = 'Task' and [System.AreaPath]='Test Agile Project'""
}",

Source = Json.Document(Web.Contents(url,[
         Headers = [#"Authorization"= AuthKey ,
                    #"Content-Type"= "application/json"],
            Content = Text.ToBinary(body)
            ]
        ))
    
in
   Source

 and the eeror i am getting is error.JPG

 

can you please help what is wrong with this setup

@yakkalipavan

Try putting rest of the relative path befroe content. It should work.

 

Regards,
Sahil

Hi 

@sahilhira162  

 

 

Thanks  you very much. i could able to connect now by setting the data source to anonymous

I got the same error I tried connecting as an anonymous user but receiving the following error"We couldn't authenticate with the credentials provided.Please try again".Could anyone please help.

Hi  Santhanakrishna,

 

I got the same error I tried connecting as an anonyms user but receiving the following error

"We couldn't authenticate with the credentials provided.Please try again"

 

U got any solution for this issue, Could anyone please help.

Hello.

 

I also have the problem of accessing a web API with the post method, using an anonymous connection. I have parameter in the header that has a key, and this works great on the desktop client, but as soon as you try and refresh from the Power BI service online, it can't authenticate anonymously.

 

Does anyone have an answer for this yet?

I got the same error I tried connecting as an anonyms user but receiving the following error

"We couldn't authenticate with the credentials provided.Please try again"

Could anyone please help.

Thank you very much for the quick reply @sahilhira162.

 

Yes I tried changing the relative path as well. Still the same error. 

 

let 
AuthKey = "Basic Y2FtcHVzL2RmMjU1MGZpOmpsZWU0cmp5eWl1Mmh4bm4zeml5eHcydHZ3NWx1NHVqcm5kbDRoZm56d29uN3pjYml1dnE=",
url="https://tfs.mnsu.edu",
body="{
  ""query"": ""Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.WorkItemType] = 'Task' and [System.AreaPath]='Test Agile Project'""
}",

Source = Json.Document(Web.Contents(url,[
         Headers = [#"Authorization"= AuthKey ,
                    #"Content-Type"= "application/json"],
            Content = Text.ToBinary(body),
            RelativePath="tfs/ITS%20AppDev/Test%20Agile%20Project/_apis/wit/wiql?api-version=1.0"
            ]
        ))
    
in
   Source

 

error2.JPG

Thanks

Yakkalipavan

@yakkalipavan Make sure your data source connection for the link is set as annonymous.

 

Regards,

Sahil

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors