load arcgis online data into Power BI using OAuth2

[Song_Li]

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 

[ZoltanKelly · Accepted solution]

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]

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]

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.

Thanks!

[NasreddineD]

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

[ZoltanKelly · Accepted solution]

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

[Song_Li]

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

[TaylorCarnell1]

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)

[AshParker1]

@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"

[TaylorCarnell1]

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