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.

kapalczynski

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-oauth2/td-p/1082526

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

 

 

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"