I'm just starting this journey and it seems to me that it varies greatly depending on the vendor's API. I could not get the Web connection to work in my situation. Therefore, I jumped over to a blank query. Once there click on Advance Editor button in the middle of toolbar to get started.
As I understand it, all Rest APIs require a token by definition. However, the token may or may not expire. If a token does not expire you can retrieve it once (using a tool called PostMan) and then hardcode it in Power Query. If it expires, the following code "should" be able to retrieve it. Yes, you will need to pass header and body details.
Again, it varies, but the vendor we used, the API authentatication was within the query itself. Personally, seems like a terrible design decision, but not my call. Some APIs I believe would authentic by using the "Data Source Settings" icon within Power Query.
All the code you need is included below...
RefreshAPIToken = () as text =>
let
token_url = "https://www.example.com/api/",
//The EXACT parameters vary from API to API
//Include EscapeDataString if the parameter value include special characters
UserID = "USERID_VALUE",
Password = Uri.EscapeDataString("PASSWORD_VALUE"),
Client_ID = Uri.EscapeDataString("CLIENT_ID_VALUE"),
Client_Secret = Uri.EscapeDataString("CLIENT_SECRET_VALUE"),
Database = "DATABASE_VALUE",
body="Username="&UserID&"&Password="&Password&"&grant_type=password&Integrated=N&database="&Database & "&Client_Id=" & Client_ID & "&Client_Secret=" & Client_Secret,
params =
[
Content = Text.ToBinary(body),
RelativePath = "Token", //use address that retrieves token from specific API
Headers = [#"Content-Type"="application/x-www-form-urlencoded"]
],
WebSource = Json.Document(Web.Contents(token_url,params)),
apikey = WebSource[access_token] //use fieldname that stores token within specific API
in
apikey,
Rest API retrieve data by pages, so you will need to loop somehow to get each page:
GetPages = () =>
let
apikey = RefreshAPIToken(),
//Cycle each page adding the results, one call at a time
//This API does not give a total count of pages back, so you go until you hit null
jobsJsonPaginated = List.Generate( () =>
[counter = 1,pageResult=FetchPage(apikey,1),pageSize=0],
each [counter]<=1 or [pageSize]>0,
each [
counter = [counter] + 1 ,
pageResult = try FetchPage(apikey,counter) otherwise null,
pageSize = try List.Count(pageResult) otherwise 0
],
each [pageResult]),
jobsJsonPaginatedOut = List.RemoveNulls(jobsJsonPaginated)
in
jobsJsonPaginatedOut,
Finally, you will need to fetch each page:
FetchPage = (apikey,page) =>
let
Source =
Json.Document(
Web.Contents(
"https://www.example.com/api/Table_Name",
[
Headers=[Authorization="Bearer "&apikey, ContentType="application/json;"],
Query=[#"page"=Text.From(page),#"pageSize"="50"]
]
)
)
in
Source,
You can build a table with this code:
let
Table_Name = #table(
{"Table_Name"},
{{"Table_Name Source"}}
),
Output =
//Initial table creation
Table.AddColumn(
Table_Name,
"Column1",
each GetPages()
),
//Build additional columns into the table
#"Expanded Column1" = Table.ExpandListColumn(Output, "Column1"),
#"Expanded Column2" = Table.ExpandListColumn(#"Expanded Column1", "Column1"),
//Add all fields
#"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1", {"FIELD_NAME1", "FIELD_NAME2", "FIELD_NAME3"})
in
#"Expanded Column3"
