This is a basic connector to connect Excel (or PowerBI) to Manager api2.
let
_ExpandCols = (TableToExpand as table, optional ColumnName as text) => let
ListAllColumns = Table.ColumnNames(TableToExpand),
ColumnsTotal = Table.ColumnCount(TableToExpand),
CurrentColumnIndex = if (ColumnName = null) then 0 else List.PositionOf(ListAllColumns, ColumnName),
CurrentColumnName = ListAllColumns{CurrentColumnIndex},
CurrentColumnContent = Table.Column(TableToExpand, CurrentColumnName),
IsExpandable = if List.IsEmpty(List.Distinct(List.Select(CurrentColumnContent, each _ is record))) then false else true,
FieldsToExpand = if IsExpandable then Record.FieldNames(List.First(List.Select(CurrentColumnContent, each _ is record))) else {},
ColumnNewNames = List.Transform(FieldsToExpand, each CurrentColumnName & "." & _),
ExpandedTable = if IsExpandable then Table.ExpandRecordColumn(TableToExpand, CurrentColumnName, FieldsToExpand, ColumnNewNames) else TableToExpand,
NextColumnIndex = CurrentColumnIndex + 1,
NextColumnName = ListAllColumns{NextColumnIndex}
in
if NextColumnIndex > ColumnsTotal-1 then ExpandedTable else @_ExpandCols(ExpandedTable, NextColumnName),
_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 
