No, I had tried a couple of stuff but the issue was not resolved. I was occupied with other projects but I'm back to this and I'm still facing that issue.
Here are the complete details:
section Software;
Software.Feed = (url as text) =>
let
//then
source = Web.Contents(url, [Headers = DefaultRequestHeaders]),
json = Json.Document(source),
ColumnQuery = json,
ColumnData = ColumnQuery[data],
ColumnData1 = ColumnData{0},
Columns = ColumnData1[Columns],
#”ColumnTable” = Table.FromList(Columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded ColumnTable” = Table.ExpandRecordColumn(#”ColumnTable”, “Column1”, {“Name”}, {“Column1.Name”}),
#”Transposed ColumnTable” = Table.Transpose(#”Expanded ColumnTable”),
#”ColumnTable Headers” = Table.PromoteHeaders(#”Transposed ColumnTable”, [PromoteAllScalars=true]),
RowQuery = json,
RowData = RowQuery[data],
RowData1 = RowData{0},
Rows = RowData1[Rows],
#”RowTable” = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RowCheck = try Table.ExpandRecordColumn(#”RowTable”, “Column1”, {“Values”}, {“Column1.Values”}),
#”Expanded RowTable” = Table.ExpandRecordColumn(#”RowTable”, “Column1”, {“Values”}, {“Column1.Values”}),
#”Extracted Values” = Table.TransformColumns(#”Expanded RowTable”, {“Column1.Values”, each Combiner.CombineTextByDelimiter(“$”)(List.Transform(_, Text.From)), type text}),
#”Split RowTable” = Table.SplitColumn(#”Extracted Values”, “Column1.Values”, Splitter.SplitTextByDelimiter(“$”, QuoteStyle.Csv)),
outcome = List.Count(Table.ColumnNames(#”ColumnTable Headers”)) > List.Count(Table.ColumnNames( #”Split RowTable”)),
#”Split RowTableMod” = if outcome then Table.AddColumn(#”Split RowTable”, “Custom”, each null) else #”Split RowTable”,
#”OutputTable0″ = Table.RenameColumns(#”Split RowTableMod”, List.Zip({Table.ColumnNames(#”Split RowTableMod”),Table.ColumnNames(#”ColumnTable Headers”)})),
//else
Data = json[data],
#”DataTable” = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Column = #”DataTable”{0}[Column1],
#”ColumnTable1″ = Record.ToTable(Column),
#”ColumnTableRemovedValues” = Table.RemoveColumns( #”ColumnTable1″,{“Value”}),
#”OutputTable1″ = Table.ExpandRecordColumn(#”DataTable”, “Column1″, Table.ToList(#”ColumnTableRemovedValues”)),
visualizerQuery = Text.Contains(url, “visualizers”),
RealTimeHealthQuery = Text.Contains(url, “virtual/objects”),
SoftwareSystemsQuery = Text.Contains(url, “api/systems”),
result = if RowCheck[HasError] and visualizerQuery
then “Status: 204 No Content”
else if visualizerQuery
then #”OutputTable0″
else if RealTimeHealthQuery
then Table.ExpandRecordColumn(#”OutputTable1″, “health”, {“time”, “usage”, “summary”, “cpu”, “mem”, “disk”, “net”, “latency”, “startup”, “virt”, “vm”, “install”, “update”, “event”, “fault”, “hwproblem”, “gapps”, “gappsfocus”, “statuses”}, {“health.time”, “health.usage”, “health.summary”, “health.cpu”, “health.mem”, “health.disk”, “health.net”, “health.latency”, “health.startup”, “health.virt”, “health.vm”, “health.install”, “health.update”, “health.event”, “health.fault”, “health.hwproblem”, “health.gapps”, “health.gappsfocus”, “health.statuses”})
else if SoftwareSystemsQuery
then Table.RenameColumns(#”OutputTable1″,{{“sysGuid”, “WGUID”}})
else #”OutputTable1″
in
result;
//
// OAuth configuration settings
//
client_id = Text.FromBinary(Extension.Contents(“client_id.txt”));
username=”APIUSERNAME”;
password=”APIPASSWORD”;
redirect_uri = “CLOUDURL”;
token_uri = “TOKENURL”;
authorize_uri = “AUTHORIZEURL”;
logout_uri = “https://login.microsoftonline.com/logout.srf”;
windowWidth = 720;
windowHeight = 1024;
scope_prefix = “https://graph.microsoft.com/”;
scopes = {
“User.Read”,
“openid”
};
//
// Exported function(s)
//
[DataSource.Kind = “Software”, Publish=”Software.Publish”]
shared Software.Content = Value.ReplaceType(SoftwareNavTable, type function (url as Uri.Type) as any);
//
// Data Source definition
//
Software = [
TestConnection = (dataSourcePath) => {“Software.Content”, dataSourcePath},
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = “Software Connector”
];
//
// OAuth implementation
//
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & “?” & Uri.BuildQueryString([
client_id = client_id,
username= username,
password= password,
redirect_uri = redirect_uri,
state = state,
scope = “offline_access ” & GetScopeString(scopes, scope_prefix),
response_type = “code”, //”code, id_token, token”,
response_mode = “query”,
login = “login”
])
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an “error” field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result = if (Record.HasFields(parts, {“error”, “error_description”})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod(“authorization_code”, “code”, parts[code])
in
result;
// Called when the access_token has expired, and a refresh_token is available.
//
Refresh = (resourceUrl, refresh_token) => TokenMethod(“refresh_token”, “refresh_token”, refresh_token);
Logout = (token) => logout_uri;
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
client_id= “CLIENTID”,
scope=”SCOPE”,
grant_type=”password”,
response_type=”token”,
username=”APIUSERNAME”,
password=”APIPASSWORD”
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#”Content-type” = “application/x-www-form-urlencoded”,
#”Accept” = “application/json”
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {“error”, “error_description”})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
//
// Helper Functions
//
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, “”),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, ” “)
in
asText;
//
// UI Export definition
//
Software.Publish = [
Beta = true,
Category = “Other”,
ButtonText = { Extension.LoadString(“ButtonTitle”), Extension.LoadString(“ButtonHelp”) },
SourceImage = Software.Icons,
SourceTypeImage = Software.Icons
];
Software.Icons = [
Icon16 = { Extension.Contents(“Software16.png”), Extension.Contents(“Software20.png”), Extension.Contents(“Software24.png”), Extension.Contents(“Software32.png”) },
Icon32 = { Extension.Contents(“Software32.png”), Extension.Contents(“Software40.png”), Extension.Contents(“Software48.png”), Extension.Contents(“Software64.png”) }
];
DefaultRequestHeaders = [
#”Accept” = “application/json”
];
SoftwareImpl = (url as text) =>
let
source = Web.Contents(url, [Headers = DefaultRequestHeaders]),
json = Json.Document(source)
in
json;
RootEntities = #table({“Name”, “Query”}, {
{“Software Systems”, “/api/systems”},
{“Health Data”, “/api/visualizers/desktop/…….”},
{“User Data”, “/api/visualizers/per/…….”},
{“Software Packages”, “/api/visualizers/Desktop/…….”},
{“Storage”, “/api/visualizers/Desktop/…….”},
{“System Mobility”, “/api/visualizers/Desktop/…….”},
{“Real Time Health”, “api/virtual/objects……”}});
SoftwareNavTable = (url as text) as table =>
let
entitiesAsTable = RootEntities,
// Add Data as a calculated column
withData = Table.AddColumn(entitiesAsTable, “Data”, each Software.Feed(Uri.Combine(url, [Query])), Uri.Type),
// Add ItemKind and ItemName as fixed text values
withItemKind = Table.AddColumn(withData, “ItemKind”, each “Table”, type text),
withItemName = Table.AddColumn(withItemKind, “ItemName”, each “Table”, type text),
// Indicate that the node should not be expandable
withIsLeaf = Table.AddColumn(withItemName, “IsLeaf”, each true, type logical),
// Generate the nav table
outputTable = Table.RemoveColumns(withIsLeaf, “Query”),
navTable = Table.ToNavigationTable(outputTable, {“Name”}, “Name”, “Data”, “ItemKind”, “ItemName”, “IsLeaf”)
in
navTable;
Table.ToNavigationTable = (
table as table,
keyColumns as list,
nameColumn as text,
dataColumn as text,
itemKindColumn as text,
itemNameColumn as text,
isLeafColumn as text
) as table =>
let
tableType = Value.Type(table),
newTableType = Type.AddTableKey(tableType, keyColumns, true) meta
[
NavigationTable.NameColumn = nameColumn,
NavigationTable.DataColumn = dataColumn,
NavigationTable.ItemKindColumn = itemKindColumn,
Preview.DelayColumn = itemNameColumn,
NavigationTable.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
