Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors