Overcome the dataflows refresh limitation on Pro Workspace with Function inside the query

[teste11234]

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

    Source = try Json.Document(Web.Contents("https://api.akhsfkjaskfjsajf.io/",

                [

                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.

[teste11234 · Accepted solution]

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.

let

    getPageClientes = (page as text) =>

        let

            Source = try Json.Document(Web.Contents("https://api.sensedata.io/",

            [

            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"