Connect to AGOL Secured Service with PowerBi

[kapalczynski]

I have been successful in connecting to a public hosted service from our AGOL Enterprise Account via Power Bi.  
I used these steps: https://resource.esriuk.com/blog/how-to-use-arcgis-with-power-bi/

I can connect to that service when its secured if I bake in a Token to the URL. But that would require me to do so every time I wanted to run the Power Bi query.

I need to find a way to dynamically grab a token each time.  'Is there anyone out there'  that may have done this and can shed some light on how to request a token from PowerBi or some other way... I would be appreciative.

[LeslieUK]

Hi @kapalczynski 

This is not something I've really done before as there is a limitation in publishing Power BI reports. Maps to be published cannot contain secure or privately owned data. Maps containing private data cannot be published to the web.

Just something to keep in mind, even if you do find that this is possible!

[kapalczynski · Accepted solution]

I figured it out with this: 

The caveat is that you have to go into Developer and create an Application while you are logged in... this will give you the Client ID and Client Secret to use in Power BI... The ID and Secret will be used for Auth and will allow you to grab a token... the script does this below on the fly...

https://developers.arcgis.com/dashboard/

https://community.esri.com/t5/arcgis-online-questions/load-arcgis-online-data-into-power-bi-using-oa...

https://community.esri.com/t5/arcgis-online-questions/using-excel-to-access-arcgis-online-automated/...

let
//Custom Variables
client_id = "HMmxxxxxgUP",
client_secret = "dee3xxxxxxxxxxxxxx5c1749c60",
fsURL = "https://services.arcgis.com/p5v98kjhkhj3l7/arcgis/rest/services/Bridges/FeatureServer/0/",
whereClause = "OBJECTID>0",
fieldsOut = "*",
//tokenrequest = "https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=& client_id & '&client_secret='& client_secret & '&grant_type=client_credentials'",
tokenrequest = "https://www.arcgis.com/sharing/rest/oauth2/token/?", 
//Get Data
Token_auto = Json.Document(Web.Contents(tokenrequest & "client_id=" & client_id & "&client_secret=" & client_secret & "&grant_type=client_credentials"), 65001),
#"TokenTable" = Record.ToTable(Token_auto),
token = TokenTable{0}[Value],

Source = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=true&f=pjson&token=" & token), 65001),

#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{7}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"attributes"}, {"attributes"}),
#"Expanded attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "attributes", {"OBJECTID", "GlobalID"}, {"OBJECTID", "GlobalID"})
in
#"Expanded attributes"