Table is refreshed manually in PQ but not in "Normal" view in PBI | Lazy Evaluation?

[h4ck3ff]

Hi guys,

Im having issues with one of my tables which cant be refreshed by PowerBI. With more than 3 days of head banging I've noticed that the table is refreshing in Power Query Editor by clicking on

but when I try to close and apply 

with the following errors (where the only problematic table is "Scanner API - Get Info" as it is impossible to refresh it If Im not in PQ editor) :

That table blocks all of my other tables. The idea of the table is that it takes the ScanID and Im using this ScanID in the other table named Scanner API - Result.

I was trying to use Table.Buffer/List.Buffer, Function.InvokeAfter(), Parallel Loading... another tons of things but without success;( The table just dont want to be refreshed If Im not in PQ editor. Im really out of ideas and I will really appreciate your help.

My both queries for now are:

Scanner API - Get Info (the tables with the problem):
let
Source = PowerBIRESTAPI.Navigation(),
ScannerAdmin = Source{[Key="Functions"]}[Data],
ScannerAPIInfo = ScannerAdmin{[Key="ScannerAPIInfo"]}[Data],
BufferedWorkspaceIDs = List.Buffer(#"Loaded Workspaces"[Workspace ID]),
InvokedFunctionScannerAPIInfo1 = ScannerAPIInfo(null, BufferedWorkspaceIDs)
in
InvokedFunctionScannerAPIInfo1


and 2nd query for the Scanner API - Result:

let
Source = PowerBIRESTAPI.Navigation(),
ScannerAdmin = Source{[Key="Functions"]}[Data],
ScannerAPIStatusAndResult = ScannerAdmin{[Key="ScannerAPIStatusAndResult"]}[Data],
DelayedScannerAPIStatusAndResult = Function.InvokeAfter(() => ScannerAPIStatusAndResult(#"Scanner API - Get Info"{0}[Id of Scan]), #duration(0, 0, 0, 10)),
#"Expanded datasets" = Table.ExpandListColumn(DelayedScannerAPIStatusAndResult, "datasets"),
#"Expanded datasets1" = Table.ExpandRecordColumn(#"Expanded datasets", "datasets", {"id", "name", "tables"}, {"datasets.id", "datasets.name", "datasets.tables"}),
#"Expanded datasets.tables" = Table.ExpandListColumn(#"Expanded datasets1", "datasets.tables"),
#"Expanded datasets.tables1" = Table.ExpandRecordColumn(#"Expanded datasets.tables", "datasets.tables", {"name", "columns", "measures", "source"}, {"datasets.tables.name", "datasets.tables.columns", "datasets.tables.measures", "datasets.tables.source"}),
#"Expanded datasets.tables.columns" = Table.ExpandListColumn(#"Expanded datasets.tables1", "datasets.tables.columns"),
#"Expanded datasets.tables.columns1" = Table.ExpandRecordColumn(#"Expanded datasets.tables.columns", "datasets.tables.columns", {"name", "dataType"}, {"datasets.tables.columns.name", "datasets.tables.columns.dataType"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded datasets.tables.columns1",{"isOnDedicatedCapacity"}),
#"Expanded reports" = Table.ExpandListColumn(#"Removed Columns", "reports"),
#"Filtered Rows" = Table.SelectRows(#"Expanded reports", each true),
#"Expanded datasets.tables.measures" = Table.ExpandListColumn(#"Filtered Rows", "datasets.tables.measures"),
#"Expanded datasets.tables.measures1" = Table.ExpandRecordColumn(#"Expanded datasets.tables.measures", "datasets.tables.measures", {"name", "expression"}, {"datasets.tables.measures.name", "datasets.tables.measures.expression"}),
#"Expanded datasets.tables.source" = Table.ExpandListColumn(#"Expanded datasets.tables.measures1", "datasets.tables.source"),
#"Expanded datasets.tables.source1" = Table.ExpandRecordColumn(#"Expanded datasets.tables.source", "datasets.tables.source", {"expression"}, {"datasets.tables.source.expression"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded datasets.tables.source1", each ([datasets.id] <> null))
in
#"Filtered Rows1"

Kind regards.

[Mahesh0016]

@h4ck3ff can you please elaborate this step
BufferedWorkspaceIDs = List.Buffer(#"Loaded Workspaces"[Workspace ID]).

[h4ck3ff]

Im not sure If it is needed but I can explain you why I try it and it is because of lazy evaluation of Power Query. Some possibilities exist for eliminating that my sub-query/step fill be evaluated as non-necessary. One tip for that was using List.Buffer() over the step/function that is skipped.

In my case "Scanner API - Get Info" was skipped which includes #"Loaded Workspaces"[Workspace ID] as List, thats why I though that If Im using List.Buffer() can somehow solve the issue.

About sharing the report.. I can provide you an .pbit file but you need to be service principal on PBI because of permissions needed by API calls (If not the data in the report will not load).

[Mahesh0016]

@h4ck3ff I have a service principal so please share your .pbix file. Thank You!!

[Mahesh0016]

@h4ck3ff Please can you share your file?

[h4ck3ff]

it has been sent

[Mahesh0016]

@h4ck3ff Can you use Power Custom Connector? if yes can you share that code or file for batter understanding. Thank You!!

[h4ck3ff]

Hi @Mahesh0016 , I dont know about Power Custom Connector but in the codes provided above Im using https://github.com/migueesc123/PowerBIRESTAPI