Forum Discussion
Overcome the dataflows refresh limitation on Pro Workspace with Function inside the query
Hi guys,
I need your help on the query below.
I put the function inside the query to resolve the dataflow refresh limitation of Computed Entity to work on Pro Workspace.
But I have got this message: "We cannot convert a value of type Table to type List."
let
GetPageClientesFunction = (page as text) as list =>
let
[
RelativePath = "v2/customers?page=" & page & "&limit=1000",
Headers=[Authorization="jsfshfhsjfjsafasfjkakjfskj"]])) otherwise [customers=null],
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table",
Source = List.Generate(
() => [PAGE = 1, RESULTS = GetPageClientesFunction("1")],
each Table.RowCount(Table.SelectRows([RESULTS], each [count]>0)) >0,
each [PAGE = [PAGE] + 1, RESULTS = GetPageClientesFunction(Number.ToText([PAGE] + 1))],
each [RESULTS]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Many tks,
---
Alexandre.
I got it, I got it... I confess ChatGPT help me a lot. Here is the final solution.
I introduce List.Buffer and Table.Buffer to turn faster execution of the query.
letgetPageClientes = (page as text) =>let[RelativePath = "v2/customers?page=" & page & "&limit=1000",Headers=[Authorization="Bearer sadasfasfasfafasfasfasf="]])) otherwise [customers=null],#"Converted to Table" = Table.Buffer(Table.FromRecords({Source}),[BufferMode = BufferMode.Delayed])in#"Converted to Table",Source = List.Buffer(List.Generate(() => [PAGE = 1, RESULTS = getPageClientes("1")],each Table.RowCount(Table.SelectRows([RESULTS], each [count]>0)) >0,each [PAGE = [PAGE] + 1, RESULTS = getPageClientes(Number.ToText([PAGE] + 1))],each [RESULTS])),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"customers", "per_page", "current_page", "count", "next_page"}, {"customers", "per_page", "current_page", "count", "next_page"})in#"Expanded Column1"
1 Reply
I got it, I got it... I confess ChatGPT help me a lot. Here is the final solution.
I introduce List.Buffer and Table.Buffer to turn faster execution of the query.
letgetPageClientes = (page as text) =>let[RelativePath = "v2/customers?page=" & page & "&limit=1000",Headers=[Authorization="Bearer sadasfasfasfafasfasfasf="]])) otherwise [customers=null],#"Converted to Table" = Table.Buffer(Table.FromRecords({Source}),[BufferMode = BufferMode.Delayed])in#"Converted to Table",Source = List.Buffer(List.Generate(() => [PAGE = 1, RESULTS = getPageClientes("1")],each Table.RowCount(Table.SelectRows([RESULTS], each [count]>0)) >0,each [PAGE = [PAGE] + 1, RESULTS = getPageClientes(Number.ToText([PAGE] + 1))],each [RESULTS])),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"customers", "per_page", "current_page", "count", "next_page"}, {"customers", "per_page", "current_page", "count", "next_page"})in#"Expanded Column1"