none
Custom Connector navigation table lazy load of all tables RRS feed

  • Question

  • Hello.
    I'm currently building a custom data connector for a private API and i'm having some doubts about what is the best approach. My connector logic is:

    1. shared Example.Contents(url) calls Top Level Navigation Table function -> Example.TopLevelNav(url);

    2. Example.TopLevelNav(url) calls GetTables(url) function, that gets the List of existing tables from the API, and uses the Table.ToNavigationTable custom function. I'd post an image of the navigator but it won't let me post images yet. 

    3. In the Data Column, i would like to insert a function GetTable(url, id, params) that makes calls to the API with a WS to get the table content.

    The thing is, when i put the function GetTable() inside, power query never stops querying and it never shows my data on the navigator. Fiddler shows all the multiple calls happening but it never shows the data on PBI.
    So my question is: Is it possible for the navigator to only show the preview of all tables (maybe some rows, or only metadata), and only actually Load the desired tables when Load Button is pressed?

    Here is my M code for the functions mentioned:

    Example.TopLevelNav = (Url as text) as table =>
        let      
            NavTable = GetTables(Url)
        in
            NavTable;
    GetTables = (Url as text) as table =>
        let
            authKey = Extension.CurrentCredential()[Key], 
            baseUrl = Url & "/api/Data/GetListaConsultas",
            options = [Headers=[auth_key = authKey, #"Content-Type"="application/Json"]], 
            source = Json.Document(Web.Contents(baseUrl, options)),
            toTable = Table.FromRecords(source),
            addDataTables = Table.AddColumn(toTable, "Data", each #table({"Data"}, GetTable(Url, [Key], "[]"))),
            addItemKind = Table.AddColumn(addDataTables, "ItemKind", each "Table"),
            addItemName = Table.AddColumn(addItemKind, "ItemName", each "Table"),
            addLeaf = Table.AddColumn(addItemName, "IsLeaf", each "true"),
            NavTable = Table.ToNavigationTable(addLeaf,  {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
          in
            NavTable;


    GetTable = (Url as text, ID as text, Params as text) as table =>
        let
            authKey = Extension.CurrentCredential()[Key], 
            baseUrl = UrlCombine(Url as text, ID as text, Params as text),
            options = [Headers=[auth_key = authKey, #"Content-Type"="application/Json"]], 
            source = Json.Document(Web.Contents(baseUrl, options)herw),
            recordsTable = Table.FromRecords(source)
          in
            recordsTable;

    Thank you,

    Kind regards.

    Friday, May 17, 2019 4:05 PM

Answers

  • Hi @psych0babble, i'm sorry, i didn't see this until now... Would it still be helpful?

    Here it is:

    shared Example.Contents = Value.ReplaceType(GetTables, type function (ServiceAddress as Uri.Type) as any);
    
    GetTables = (ServiceAddress as text) as table => 
        let
            authKey = Extension.CurrentCredential()[Key],    
            baseUrl = ServiceAddress & "rest of url",
            options = [
                        Headers = [auth_key = authKey, #"Content-Type"="application/Json" ],
            source = Web.Contents(_url, options), 
            toJson = try Json.Document(source) 
                            otherwise error "This should have worked. Check log files for more details.",      
            // create a table
            toTable = Table.FromRecords(toJson),
            // inserting a function that receives parameters on the Data field of the navigation table
            addDataTable = Table.AddColumn(toTable, "Data", each (Parameters as nullable text) as table => GetData(ServiceAddress, [Key], Parameters, Page)), 
            addItemKind = Table.AddColumn(addDataTable, "ItemKind", each "Function"),
            addItemName = Table.AddColumn(addItemKind, "ItemName", each "Function"),
            addLeaf = Table.AddColumn(addItemName, "IsLeaf", each "false"), 
            NavTable = Table.ToNavigationTable(addLeaf,  {"Key"}, "Name", "Data" , "ItemKind", "ItemName", "IsLeaf")
          in
            NavTable;
    
    
    GetData = (ServiceAddress as text, QueryID as text, Parameters as nullable text, Page as number) as table =>
        let
            authKey = Extension.CurrentCredential()[Key], 
            baseUrl = UrlBuild(ServiceAddress, QueryID, Parameters, Page),
            options = [Headers=[auth_key = authKey, #"Content-Type"="application/Json"]], 
            _url = ValidateUrlScheme(baseUrl),     
            source = Binary.Buffer(Web.Contents(baseUrl, options)),
            jsonParsed =  try Json.Document(source) otherwise "Didn't work, try again later.",
            recordsTable = Table.FromRecords(jsonParsed),
            objects = #table({"Name",           "Key",           "Data",                 "ItemKind", "ItemName", "IsLeaf"},
                    {{"Query",    "GetQueryInfo",      recordsTable, "Table",     "Table",    true}}),
            navTable = Table.ToNavigationTable(objects,  {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf"),
            query = navTable[Data]{0}
        in
            query;

    • Marked as answer by Dreekun Tuesday, October 8, 2019 3:00 PM
    • Unmarked as answer by Dreekun Tuesday, October 8, 2019 3:00 PM
    • Marked as answer by Dreekun Tuesday, October 8, 2019 3:00 PM
    Tuesday, October 8, 2019 2:59 PM

All replies

  • Solved, thanks.
    Friday, May 24, 2019 3:35 PM
  • Hi @Dreekun,

    Could you please provide me with your solution. I want to attempt something similar.

    Thanks!

    Thursday, June 27, 2019 10:38 AM
  • Hi @psych0babble, i'm sorry, i didn't see this until now... Would it still be helpful?

    Here it is:

    shared Example.Contents = Value.ReplaceType(GetTables, type function (ServiceAddress as Uri.Type) as any);
    
    GetTables = (ServiceAddress as text) as table => 
        let
            authKey = Extension.CurrentCredential()[Key],    
            baseUrl = ServiceAddress & "rest of url",
            options = [
                        Headers = [auth_key = authKey, #"Content-Type"="application/Json" ],
            source = Web.Contents(_url, options), 
            toJson = try Json.Document(source) 
                            otherwise error "This should have worked. Check log files for more details.",      
            // create a table
            toTable = Table.FromRecords(toJson),
            // inserting a function that receives parameters on the Data field of the navigation table
            addDataTable = Table.AddColumn(toTable, "Data", each (Parameters as nullable text) as table => GetData(ServiceAddress, [Key], Parameters, Page)), 
            addItemKind = Table.AddColumn(addDataTable, "ItemKind", each "Function"),
            addItemName = Table.AddColumn(addItemKind, "ItemName", each "Function"),
            addLeaf = Table.AddColumn(addItemName, "IsLeaf", each "false"), 
            NavTable = Table.ToNavigationTable(addLeaf,  {"Key"}, "Name", "Data" , "ItemKind", "ItemName", "IsLeaf")
          in
            NavTable;
    
    
    GetData = (ServiceAddress as text, QueryID as text, Parameters as nullable text, Page as number) as table =>
        let
            authKey = Extension.CurrentCredential()[Key], 
            baseUrl = UrlBuild(ServiceAddress, QueryID, Parameters, Page),
            options = [Headers=[auth_key = authKey, #"Content-Type"="application/Json"]], 
            _url = ValidateUrlScheme(baseUrl),     
            source = Binary.Buffer(Web.Contents(baseUrl, options)),
            jsonParsed =  try Json.Document(source) otherwise "Didn't work, try again later.",
            recordsTable = Table.FromRecords(jsonParsed),
            objects = #table({"Name",           "Key",           "Data",                 "ItemKind", "ItemName", "IsLeaf"},
                    {{"Query",    "GetQueryInfo",      recordsTable, "Table",     "Table",    true}}),
            navTable = Table.ToNavigationTable(objects,  {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf"),
            query = navTable[Data]{0}
        in
            query;

    • Marked as answer by Dreekun Tuesday, October 8, 2019 3:00 PM
    • Unmarked as answer by Dreekun Tuesday, October 8, 2019 3:00 PM
    • Marked as answer by Dreekun Tuesday, October 8, 2019 3:00 PM
    Tuesday, October 8, 2019 2:59 PM