Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
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]
Logic what i need:
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.