Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
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.
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
User | Count |
---|---|
5 | |
5 | |
3 | |
2 | |
2 |
User | Count |
---|---|
9 | |
7 | |
5 | |
4 | |
4 |