Forum Discussion
Anonymous access
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.
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 :)
Great idea, thank you.
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),
