load arcgis online data into Power BI using OAuth2
529
1
01-05-2023 08:27 AM
by
All,
I am attempting to create a query in PowerBI using this code. I replaced Client_ID, Client_Secret, fsURL and the token request.
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"
Now I am running into this error. Does anyone know a possible solution?
by
All,
I am attempting to create a query in PowerBI using this code. I replaced Client_ID, Client_Secret, fsURL and the token request.
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"
Now I am running into this error. Does anyone know a possible solution?
