This is a basic connector to connect Excel (or PowerBI) to Manager api2.
let
_GetFieldNames = (list) => let
allRecords = List.Select(list, each _ is record),
isEmpty = List.IsEmpty(List.Distinct(allRecords)),
allFieldNames = List.Union(List.Transform(allRecords, each Record.FieldNames(_))),
uniqueFieldNames = List.Distinct(allFieldNames)
in
if isEmpty then {} else uniqueFieldNames,
_ExpandCols = (#"Table to Expand" as table, optional #"Column Name" as text) => let
tbl = #"Table to Expand",
colName = #"Column Name",
allCols = Table.ColumnNames(tbl),
colCount = Table.ColumnCount(tbl),
thisColIdx = if (colName = null) then 0 else List.PositionOf(allCols, colName),
thisColName = allCols{thisColIdx},
thisColContent = Table.Column(tbl, thisColName),
thisFieldList = _GetFieldNames(thisColContent),
isExpandable = not List.IsEmpty(thisFieldList),
colNames = if isExpandable then List.Transform(thisFieldList, each thisColName & "." & _) else {},
newTable = if isExpandable then Table.ExpandRecordColumn(tbl, thisColName, thisFieldList, colNames) else tbl,
nextColIdx = thisColIdx + 1,
nextColName = allCols{nextColIdx}
in
if nextColIdx > colCount - 1 then newTable else @_ExpandCols(newTable, nextColName),
_KebabToCamel = (string as text) => let
camelCaseString = Text.Replace(
Text.ReplaceRange(
Text.Proper(
Text.Replace(string,"-"," ")
),
0,1,Text.Lower(Text.At(string,0))
)," ","")
in
camelCaseString,
_SingularKebab = (string as text) => let
singularString = Text.Replace(Text.Start(string,Text.Length(string)-1),"-and-","-or-")
in
singularString,
_ManagerApi2_Connect = (#"Source Path" as text, #"Request Headers" as record) => let
result = Json.Document(Web.Contents(#"Source Path" , [Headers = #"Request Headers"]))
in
result,
_ManagerApi2_GetRecords = (
#"Manager Server Address" as text,
#"Access Token Secret" as text,
Route as text,
#"Expand Object" as logical,
#"Page Size" as nullable number,
#"Field List" as nullable text
) => let
source = #"Manager Server Address" & "/" & Route,
headers = [
#"accept" = "*/*",
#"content-type" = "application/json",
#"x-api-key" = #"Access Token Secret"
],
singularKebab = _SingularKebab(Route),
pathSummary = _ManagerApi2_Connect(source, headers),
recordField = _KebabToCamel(Route),
pageSize = if #"Page Size" = 0 or #"Page Size" = null then pathSummary[totalRecords] else #"Page Size",
params1 = Record.AddField([], "pageSize", Text.From(pageSize)),
params2 = try Record.AddField(params1, "fields", Text.Split(#"Field List",",")) otherwise params1,
tabView = Table.FromRecords(
Record.Field(Json.Document(Web.Contents(source, [Headers = headers, Query = params2])), recordField)
),
result = try
_ExpandCols(_ExpandCols(Table.AddColumn(tabView,Text.Proper(Text.Replace(singularKebab,"-"," ")),
each Json.Document(Web.Contents(#"Manager Server Address" & "/" & singularKebab & "-form/" & [key], [Headers = headers]))
))) otherwise tabView
in
if #"Expand Object" = true then result else tabView
in
_ManagerApi2_GetRecords
Make sure that the authentication for this source is set to “Anonymous”.
It’s quite basic, but it gives the user a starting point where he/she can use Power Query Navigation to arrive at his destination.
I was planning to release a full connector function with dynamic routes, however, I’m facing an authorization issue with Power Query, since logging in to “/api2” requires basic credentials, while accessing anything beyond that requires the x-api-key.
Power Query cannot handle that gracefully and it will cause it to prompt the user to set credentials to anonymous everytime the route changes. This results in an annoying user experience, so I would leave the full connector until I can sort this issue.
For now, this should do just fine and everyone is welcome to contribute to this connector!
Enjoy 
