Power Query making multiple rest calls

[Mikko2]

Hi all!

I have quite a large json data array (with varying depth) which I want to use in PowerBI. I noticed that the Web.Contents call gets made three times to the rest api.

Is it something in my steps that makes PowerBI desktop make multiple calls to the api as I refresh the the data?

And is there a way to tell PowerBI to use the result from the inital call and not make any more calls for the subsequent steps?

• If  I remove all the steps but first only one call gets made, so I suspect that some of the steps force PowerBI to make the call again.
• If I remove the second third line '#"Parsed JSON" = Json.Document(Source),' I get the error 'Expression.Error: We cannot convert the value "[{"Sess":"14574..." to type List.'. So I need to have the Json.Document twice to transform the json into a list. So that line seems necessary.
• Fourth line converts the jsonArray which is parsed as a list to a table, which again is necessary.
• From then on its mostly expanding the columns (Json nodes).

let
    Source = Json.Document(Web.Contents("http://localhost:4000/api/platform/request/getjsonbyprocessname/" & TextForQueries, [Timeout=#duration(0, 0, 10, 0)])),
    #"Parsed JSON" = Json.Document(Source),
    #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Sess", "Elements", "Proc", "Url"}, {"Column1.Sess", "Column1.Elements", "Column1.Proc", "Column1.Url"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"Column1.Sess", Order.Ascending}}),
    #"Expanded Column1.Elements" = Table.ExpandListColumn(#"Sorted Rows", "Column1.Elements"),
    #"Expanded Column1.Elements1" = Table.ExpandRecordColumn(#"Expanded Column1.Elements", "Column1.Elements", {"Id", "Data", "Name", "Type", "Key", "Children", "DefaultValue"}, {"Column1.Elements.Id", "Column1.Elements.Data", "Column1.Elements.Name", "Column1.Elements.Type", "Column1.Elements.Key", "Column1.Elements.Children", "Column1.Elements.DefaultValue"}),
    #"Expanded Column1.Elements.Children" = Table.ExpandListColumn(#"Expanded Column1.Elements1", "Column1.Elements.Children"),
    #"Expanded Column1.Elements.Children1" = Table.ExpandRecordColumn(#"Expanded Column1.Elements.Children", "Column1.Elements.Children", {"Id", "Data", "Name", "Type", "Key", "Children", "DefaultValue"}, {"Column1.Elements.Children.Id", "Column1.Elements.Children.Data", "Column1.Elements.Children.Name", "Column1.Elements.Children.Type", "Column1.Elements.Children.Key", "Column1.Elements.Children.Children", "Column1.Elements.Children.DefaultValue"}),
    #"Expanded Column1.Elements.Children.Children" = Table.ExpandListColumn(#"Expanded Column1.Elements.Children1", "Column1.Elements.Children.Children"),
    #"Expanded Column1.Elements.Children.Children1" = Table.ExpandRecordColumn(#"Expanded Column1.Elements.Children.Children", "Column1.Elements.Children.Children", {"Id", "Data", "Name", "Type", "Key", "Children", "DefaultValue"}, {"Column1.Elements.Children.Children.Id", "Column1.Elements.Children.Children.Data", "Column1.Elements.Children.Children.Name", "Column1.Elements.Children.Children.Type", "Column1.Elements.Children.Children.Key", "Column1.Elements.Children.Children.Children",  "Column1.Elements.Children.Children.DefaultValue"})
in
    #"Expanded Column1.Elements.Children.Children1"

I'm using Power BI Desktop and creating the query in Power Query Editor on my dev machine. My rest Api is in a project running in Visual Studio also on my machine. The back end code in in the Rest Api is then calling the database on a server. As I debug with visual studio the api gets three calls upon PoewrBI refresh. But when I call the same api with Postman only one call gets made as expected.

[Anonymous]

Here is the code for my data connector. I have also attached screenshots from PowerBI and Fiddler.

section DHIS2;

[DataSource.Kind="DHIS2", Publish="DHIS2.Publish"]
shared DHIS2.Contents = Value.ReplaceType(DHIS2.TopLevelNav, type function (url as Uri.Type) as any);

//***This is my top level navigation table. At this point no calls to the API have been made***//
DHIS2.TopLevelNav = (url as text) as table =>
    let      
        objects = #table(
            {"Name",           "Key",           "Data",                 "ItemKind", "ItemName", "IsLeaf"},
            {{"SQL Views",     "sqlviews",      CreateSQLViewsNav(url), "Feed",     "Table",    false}
            }),
       NavTable = Table.ToNavigationTable(objects,  {"Key"}, "Name", "Data", "ItemKind", "ItemKind", "IsLeaf")
    in
        NavTable;

//***Here, I want to get my 'sqlViews' via the api and display them as level 2 navigation items***//
//***However, once the 'SQL Views' folder is expanded, all the views (and their data) are fetched***//
CreateSQLViewsNav = (url as text) as table =>
    let
        content = Web.Contents(url & "/api/sqlViews?paging=false"),
        json = Json.Document(content),
        toTable = Record.ToTable(json),        
        expandViewsList = Table.ExpandListColumn(toTable, "Value"),
        expandRecords = Table.ExpandRecordColumn(expandViewsList, "Value", {"displayName","id"}),
        addItemKind = Table.AddColumn(expandRecords, "ItemKind", each "Table"),
        addDataTables = Table.AddColumn(addItemKind, "Data", each CreateSQLViewNav(#table({"id"},{{[id]}}), [id], url)),
        addLeaf = Table.AddColumn(addDataTables, "IsLeaf", each "false"),        
        NavTable = Table.ToNavigationTable(addLeaf,  {"id"}, "displayName", "Data", "ItemKind", "ItemKind", "IsLeaf")      
    in
        NavTable;

//***Ideally, this call should only be made when a user navigates to a specific view****//
CreateSQLViewNav = (idrow as table, id as text, url as text) as table =>
    let
        viewurl = url & "/api/sqlViews/" & id & "/data.csv",                 
        sqlViewTable = GetDataTable(viewurl),
        addNames = Table.AddColumn(idrow, "Name", each [id]),
        addDataTables = Table.AddColumn(addNames, "Data", each sqlViewTable),        
        addItemKind = Table.AddColumn(addDataTables, "ItemKind", each "Function"),
        adddItemName = Table.AddColumn(addItemKind, "ItemName", each "Table"),
        addLeaf = Table.AddColumn(adddItemName, "IsLeaf", each "true"),       
        NavTable = Table.ToNavigationTable(addLeaf,  {"id"}, "id", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        NavTable;

I hope this helps!

Regards,

Lewis