load arcgis online data into Power BI using OAuth2

[aishak12]

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? 

[v-yueyunzh-msft]

Hi, @aishak12 

According to your prompt, he means that it returns HTML code, not the corresponding string in JSON format.
For this kind of question, my guess is that when Power Query calls your URL, you jump to an HTML page.
The reason for this may be that to access your URL connection is to enter the username and password to log in before you can access, if there is no login or the token does not meet the conditions, it will jump to the corresponding login interface and return an HTML page.

For this kind of problem, you need to determine whether your URL access can return the corresponding data through the token, test the connectivity of the interface, and then try to call it with this code.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly