load arcgis online data into Power BI using OAuth2

4690
9
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
9 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"

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

AW_Grove
Occasional Contributor

Hi @Song_Li and @ZoltanKelly  - how did you get Power BI to recognize the token at the end of the url? When I look at the token table it has generated, but I am still getting an error that credentials are required.

AW_Grove_0-1672948382184.png

AW_Grove_1-1672948443245.png

 

Thanks!

 

 

0 Kudos
NasreddineD
New Contributor II

Hi, I followed the steps and it works very well.

Now I want to publish the power bi dashboard once the map is loaded, but it is asking for ArcGis account credentials to the end user...

How can I solve this? The end users (multiple) are not part of my organisation, they will just visualize the map and not do any modification. How can I share it?

Thank you

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

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
TaylorCarnell1
New Contributor III

Thanks for this @ZoltanKelly, works well. 

Just a quick note, I don't know if it's changed, or it's because I'm targeting enterprise instead of AGOL, but the features were index 1. 
Also, if you extract the fields (index 2 for me) you can extract the field names with List.Accumulate and pass these to ExpandRecordColumn to avoid having to hardcode the field names.

    source = Json.Document(Web.Contents(queryURL), 65001),
    
    //Process Data 
    LayerDataTable = Record.ToTable(source),
    features = LayerDataTable{1}[Value],
    fields = LayerDataTable{2}[Value],
    fieldNames = List.Accumulate( fields, {}, (s,c)=> List.Combine({ s, {Record.Field(c,"name")} }) ),
    featuresTable = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedColumn = Table.ExpandRecordColumn(featuresTable, "Column1", {"attributes"}, {"attributes"}),
    expandedAttributes = Table.ExpandRecordColumn(expandedColumn, "attributes", fieldNames)

 

0 Kudos
AshParker1
New Contributor

@TaylorCarnell1 , would you be able to provide the url you are using for accessing Enterprise?  I am using ArcGIS Enterprise too and I cant seem to nail what the URL for requesting the token is.  I have an app I set up in the portal.

If I use this:

 

tokenrequest = "https://gisportal.<MY DOMAIN>/portal/sharing/rest/generateToken/?client_id=" & client_id & "&client_secret=" & client_secret & "&grant_type=client_credentials",

 

I get this error:

"DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

If I use this:

tokenrequest = "https://gisportal.<MY DOMAIN>/portal/sharing/rest/oauth/token/?client_id=" & client_id & "&client_secret=" & client_secret & "&grant_type=client_credentials",

 

 

I get "(400): Bad Request"

0 Kudos
TaylorCarnell1
New Contributor III

The second one, but ../oauth2/..

0 Kudos