load arcgis online data into Power BI using OAuth2

250
4
Jump to solution
07-25-2021 04:57 AM
Labels (1)
Song_Li
New Contributor III

Hi, I am trying to load a table feature into Power BI for some statistics (like daily/monthly total, or by day of week etc.). I created an OAuth2 APP. My intention is to try web query in PowerBI using the Authorise and Token API, but not sure how. Wondering anyone has done this successfully?

Cheers,

Song 

0 Kudos
2 Solutions

Accepted Solutions
ZoltanKelly
New Contributor III

so short of it is something like this. create a new blank query and dump in below. you need to create a client ID and secret first for your account.

there's a long thread somewhere on this i'll try and dig it up

 

let
//Custom Variables
client_id = "xxxx",
client_secret = "xxxx",
fsURL = https://services5.arcgis.com/12345678990/ArcGIS/rest/services/ServiceName/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",

//Get Data

Token_auto = Json.Document(Web.Contents(tokenrequest), 65001),
#"TokenTable" = Record.ToTable(Token_auto),
token = TokenTable{0}[Value],

Source = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=false&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", "Trace_Catchment_Name", "GlobalID", "Site", "Type", "Shape__Area", "Shape__Length", "Population"}, {"OBJECTID", "Trace_Catchment_Name", "GlobalID", "Site", "Type", "Shape__Area", "Shape__Length", "Population"})
in
#"Expanded attributes"

View solution in original post

ZoltanKelly
New Contributor III

Hi, found that other thread - see here. this has all the details and is then how I cribbed down to above. it's regarding Excel, but any MS app using power query is applicable (Excel/PowerBI/whatever). Using Excel to access Arcgis online (automated) - Esri Community

View solution in original post

0 Kudos
4 Replies
ZoltanKelly
New Contributor III

so short of it is something like this. create a new blank query and dump in below. you need to create a client ID and secret first for your account.

there's a long thread somewhere on this i'll try and dig it up

 

let
//Custom Variables
client_id = "xxxx",
client_secret = "xxxx",
fsURL = https://services5.arcgis.com/12345678990/ArcGIS/rest/services/ServiceName/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",

//Get Data

Token_auto = Json.Document(Web.Contents(tokenrequest), 65001),
#"TokenTable" = Record.ToTable(Token_auto),
token = TokenTable{0}[Value],

Source = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=false&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", "Trace_Catchment_Name", "GlobalID", "Site", "Type", "Shape__Area", "Shape__Length", "Population"}, {"OBJECTID", "Trace_Catchment_Name", "GlobalID", "Site", "Type", "Shape__Area", "Shape__Length", "Population"})
in
#"Expanded attributes"

View solution in original post

Song_Li
New Contributor III

Thanks, Zoltan. finally got time to test this and it works. there are two things users need to pay attention to:

1. to use this method, the privacy setting at PowerBI has to be "ignore all";

2. From above you can image, this is not a very secure method by sharing the credential;

If anyone knows a more secure way (allowing user to login with their credential), please let me know, many thanks. 

Cheers,

Song

0 Kudos
ZoltanKelly
New Contributor III

Hi, found that other thread - see here. this has all the details and is then how I cribbed down to above. it's regarding Excel, but any MS app using power query is applicable (Excel/PowerBI/whatever). Using Excel to access Arcgis online (automated) - Esri Community

View solution in original post

0 Kudos
Song_Li
New Contributor III

Thank you Zoltan Kelly, as I mention above that I get it working. The only issue is that I build this PowerBI project and I want to share the project with my colleague without sharing the secret. Is it possible to actually let them use their own login for 2 factor authentication to get access to the data within Power BI? Cheers, Song

0 Kudos