cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bessonnizza
Helper II
Helper II

List.Generate loop

Hello there,

 

I am trying to implement integration with our API. The last thing left to do is to write an iterator that will call the loop if the API returned not the last portion of statistics. Here's what I'm trying to do:

  1. Сall fnGetStatatistics
  2. A response is returned.
  3. Check if the response contains column ChunkInfo.IsFinalChunk = "FALSE"
  4. If FALSE, call fnBuildNextChunk then go p.3 and return
  5. If TRUE, return the results.

Screenshot_1.png

 

 

Here's my request:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    
    //request 1 - SessionId
    SessionId = Table.AddColumn(Source, "Authorization", each fnAuthorization()),
    #"Removed Columns" = Table.RemoveColumns(SessionId,{"Column1"}),
    #"Expanded Authorization" = Table.ExpandTableColumn(#"Removed Columns", "Authorization", {"SessionId"}, {"SessionId"}),
    
    //request 2 - StatisticsSessionId
    StatisticsSessionId = Table.AddColumn(#"Expanded Authorization", "StartStatisticsSession", each fnStartStatisticsSession([SessionId])),
    #"Expanded StartStatisticsSession" = Table.ExpandTableColumn(StatisticsSessionId, "StartStatisticsSession", {"StatisticsSessionId"}, {"StatisticsSessionId"}),
    
    //request 3 - NavigationFiltration
    NavigationFiltration = Table.AddColumn(#"Expanded StartStatisticsSession", "NavigationFiltration", each fnNavigationFiltration([SessionId], [StatisticsSessionId])),
    #"Expanded NavigationFiltration" = Table.ExpandRecordColumn(NavigationFiltration, "NavigationFiltration", {"ErrorText", "Status"}, {"NavigationFiltration.ErrorText", "NavigationFiltration.Status"}),
    #"Expanded NavigationFiltration.Status" = Table.ExpandRecordColumn(#"Expanded NavigationFiltration", "NavigationFiltration.Status", {"Value"}, {"NavigationFiltration.Status.Value"}),
    
    //request 4 - AddStatisicsRequest
    AddStatisicsRequest = Table.AddColumn(#"Expanded NavigationFiltration.Status", "AddStatisicsRequest", each fnTrackPeriodsMileage_AddStatisicsRequest([SessionId], [StatisticsSessionId], [NavigationFiltration.Status.Value])),
    #"Expanded AddStatisicsRequest" = Table.ExpandRecordColumn(AddStatisicsRequest, "AddStatisicsRequest", {"ErrorText", "Status"}, {"AddStatisicsRequest.ErrorText", "AddStatisicsRequest.Status"}),
    #"Expanded AddStatisicsRequest.Status" = Table.ExpandRecordColumn(#"Expanded AddStatisicsRequest", "AddStatisicsRequest.Status", {"Value"}, {"AddStatisicsRequest.Status.Value"}),
    
    //request 5 - StartBuild
    StartBuild = Table.AddColumn(#"Expanded AddStatisicsRequest.Status", "StartBuild", each fnStartBuild([SessionId], [StatisticsSessionId], [AddStatisicsRequest.Status.Value])),
    #"Expanded StartBuild" = Table.ExpandRecordColumn(StartBuild, "StartBuild", {"Value"}, {"StartBuild.Value"}),
    
    //request 5 - GetStatistics
    #"Invoked Custom Function3" = Table.AddColumn(#"Expanded StartBuild", "test_loop", each test_loop([SessionId], [StatisticsSessionId], [StartBuild.Value])),
    test_loop1 = #"Invoked Custom Function3"{0}[test_loop]
in
    test_loop1

 

It seems that i need to use List.Generate to get loop, but i'm not understand how to do it.

7 REPLIES 7
artemus
Microsoft
Microsoft

Yes, you can use List.Generate.

 

I generally find it easier to just write the code without it.

Your code would look something like:

let getChunk = (chunkInfo) =>

   if ChunkInfo[IsFinalChunk] then

      {chunkInfo[Chunk]}

   else

      {chunkInfo[Chunk]} & @getChunk(fnBuildNextChunk(chunkInfo))

 

 

Ty for reply @artemus 

 

I can use List.Generate if i know how many times function should start. For example ChunkCount = Number.Abs(
Duration.Days(Date.FromText(#"Start") - Date.FromText(#"End"))). So List.Generate will be:

= List.Generate(
        ()=>[i=0, Statistics= each fnGetStatistics([SessionId], [StatisticsSessionId], [StartBuild.Value])],
        each [i] < ChunkCount,
        each [i=[i]+1, Statistics= each fnGetStatistics([SessionId], [StatisticsSessionId], [StartBuild.Value])]
)

 

But here i need to write condition which check result of the previous step of iteration. In my case the result stored in column ChunkInfo.IsFinalChunk (TRUE/FALSE) which i invoke fnGetStatistics function:

//request 5 - GetStatistics
    #"Invoked Custom Function3" = Table.AddColumn(#"Expanded StartBuild", "GetStatistics", each fnGetStatistics([SessionId], [StatisticsSessionId], [StartBuild.Value])),
    result = #"Invoked Custom Function3"{0}[fnGetStatistics]

2.png

 

 

 

 

 

 

Logic what i need:

1.png

 

I'm not understand how i can refer to column in a result to run recursion/List.Generate/List.Accumulate. 

If I understand your problem, just replace 

each [i] < ChunkCount,

with the condition

each not [Statistics][ChunkInfo.IsFinalChunk] 

 

Its not working 😞 get an error:

 

Expression.Error: We cannot convert a value of type Function to type List.
Details:
Value=[Function]
Type=[Type]

 

Think i have big problems syntax 😓 Have another ideas?

 

 

result
  = List.Generate(
    () => [GetStatistics = each fnGetStatistics(
        [SessionId], 
        [StatisticsSessionId], 
        [StartBuild.Value]
      )], 
    each fnGetStatistics{0}[GetStatistics]{0}[IsFinalChunk] = true, 
    each [GetStatistics = each fnGetStatistics(
        [SessionId], 
        [StatisticsSessionId], 
        [StartBuild.Value]
      )]
  )

 

 

 

It will probably work if you remove 

fnGetStatistics{0}

 and just have:

[GetStatistics]{0}[IsFinalChunk] = false

 

To be clear {0} means get the table/list first row. It doesn't work on functions. 

 

The result of the 3rd/1st argument in List.Generate is the input to the second parameter.

 

The flow is:

1. 1st parameter

2. Does 2nd parameter return true when passed the result of Step 1?

- Yes: continue

- No: stop

3. Add to list: Invoke 3rd parameter when passed the result of Step 1.

4. Does 2nd parameter return true when passed the result of the Step 3?

- Yes: continue

- No: stop

5. Add to list: Add to list: Invoke 3rd parameter when passed the result of Step 3.

6. Go to Step 4

Thank you for your patience @artemus 

 

Changed and get error:

Expression.Error: The name 'GetStatistics' wasn't recognized. Make sure it's spelled correctly

 

Result
  = List.Generate(
    () => [GetStatistics = each fnGetStatistics(
        [SessionId], 
        [StatisticsSessionId], 
        [StartBuild.Value]
      )], 
    each GetStatistics{0}[IsFinalChunk] = true, 
    each [GetStatistics = each fnGetStatistics(
        [SessionId], 
        [StatisticsSessionId], 
        [StartBuild.Value]
      )]
  )

 

Try this one, but result 0 data

 

//step 5
GetStatistics
  = Table.AddColumn(
    #"Expanded StartBuild", 
    "GetStatistics", 
    each fnGetStatistics([SessionId], [StatisticsSessionId], [StartBuild.Value])
  )
//step 6
Result
  = List.Generate(
    () => [Data = GetStatistics], 
    each GetStatistics{0}[GetStatistics]{0}[IsFinalChunk] = true, 
    each [Data = GetStatistics]
  )

 

 

If the error you are getting is The name 'GetStatistics' wasn't recognized. Make sure it's spelled correctly

 

It means you didn't put GetStatistics in [], like [GetStatistics].

 

A: a variable name called A

[A]: Shorthand for _[A]

_[A]: The column named "A" in the row/table with variable name "_"

each: Shorthand for (_) =>

(x) => x + 1: A function which takes 1 parameter x and returns x + 1.

() => [GetStatistics = each fnGetStatistics(
        [SessionId], 
        [StatisticsSessionId], 
        [StartBuild.Value]
      )]

Means:

A function which takes 0 parameters and returns a row with 1 column:

Column GetStatistics: is a function which has a row/table input and calls the function fnGetStatistics with parameters taken from the cell/column from the input using the columns "SessionId", "StatisticsSessionId", "StartBuild.Value"

 

GetStatistics{0}[IsFinalChunk] = true

Means:

From the variable/query named GetStatistics: Check if the first row, column IsFinalChunk is true. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors