topic Re: load arcgis online data into Power BI using OAuth2 in ArcGIS Online Questions

load arcgis online data into Power BI using OAuth2

Song_Li — Sun, 25 Jul 2021 11:57:29 GMT

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

Re: load arcgis online data into Power BI using OAuth2

ZoltanKelly — Wed, 04 Aug 2021 03:47:13 GMT

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"

Re: load arcgis online data into Power BI using OAuth2

Song_Li — Mon, 16 Aug 2021 21:28:21 GMT

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

Re: load arcgis online data into Power BI using OAuth2

ZoltanKelly — Sun, 19 Sep 2021 01:30:38 GMT

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

Re: load arcgis online data into Power BI using OAuth2

Song_Li — Mon, 20 Sep 2021 02:33:07 GMT

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

Re: load arcgis online data into Power BI using OAuth2

AW_Grove — Thu, 05 Jan 2023 19:54:40 GMT

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!

Re: load arcgis online data into Power BI using OAuth2

TaylorCarnell1 — Fri, 20 Jan 2023 06:40:12 GMT

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)

Re: load arcgis online data into Power BI using OAuth2

AshParker1 — Fri, 03 Feb 2023 22:15:38 GMT

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

Re: load arcgis online data into Power BI using OAuth2

TaylorCarnell1 — Mon, 06 Feb 2023 10:01:31 GMT

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

Re: load arcgis online data into Power BI using OAuth2

NasreddineD — Sat, 23 Dec 2023 15:26:53 GMT

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