Iterate over rows of a column as part of the RelativePath within a List.Generate() for API call

[MKE]

Hello,

I have a column with unique IDs.

I need to pass these IDs as part of the RelativePath in an API call using List.Generate() to return paginated results (offset,limit)

In OOP this would be something similar to: "for every ID, perform the GET request"

When I run the script below, the result is a list with ERROR as the record. If I expand the column, only the first ID returns the results and the rest ERRORs.

Here is my M script:

let
//This is the column with team Ids
    Source = 
        Json.Document(
            Web.Contents("https://api.dashpivot.com/", 
                    [
                        RelativePath = "companies/projects/teams/all",
                        Headers=[
                            Authorization="JWT xxxxxxx"
                            ]
                    ]
                )
                ),
    Source1 = Source{0},
    projects = Source1[projects],
    #"Converted to Table" = Table.FromList(projects, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teams"}, {"Column1.teams"}),
    #"Expanded Column1.teams" = Table.ExpandListColumn(#"Expanded Column1", "Column1.teams"),
    #"Expanded Column1.teams1" = Table.ExpandRecordColumn(#"Expanded Column1.teams", "Column1.teams", {"id"}, {"id"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.teams1",{{"id", type text}}),
    Startdate = Date.AddDays(
                    DateTime.Date(
                        DateTime.AddZone(DateTime.LocalNow(),10)), 
                        -#"Start Days"
                        ),
    Enddate = Date.AddDays(
                    DateTime.Date(
                        DateTime.AddZone(DateTime.LocalNow(),10)), 
                        #"End Days"
                        ),
//Adding the column with List.Generate to iterate over the IDs
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each 
    
    List.Generate( () =>
       [
      
        Result=
        try Json.Document(
                Web.Contents(
                    "https://api.dashpivot.com/v1/",
                    [
                        RelativePath = "teams/"&[id]&"/photos?",
                        Query = [
                            limit="120",
                            offset=Number.ToText(OffsetValue),
                            startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
                            endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
                          ],        
                        Headers=[
                            Authorization="JWT xxxxx"
                        ]
                   ]
                )
            )otherwise try null, Offset = OffsetValue],
        each not List.IsEmpty([Result]),
        each 
        [
      
        Result=
        try Json.Document(
                Web.Contents(
                    "https://api.dashpivot.com/v1/",
                    [
                        RelativePath = "teams/"&[id]&"/photos?",
                        Query = [
                            limit="120",
                            offset=Number.ToText([Offset]+120),
                            startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
                            endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
                          ],        
                        Headers=[
                            Authorization="JWT xxxxxx"
                        ]
                   ]
                )
            )otherwise try null, Offset = [Offset]+120],
        each [Result]
        )
      )

[ImkeF · Accepted solution]

Hi @MKE ,
unfortunately List.Generate can be a bit hard to debug. But it could be an ambiguity-problem here, as you are using it in a Table.AddColumn - operation. Try to replace the each by an explicite function definition ( (x)=> ) to get rid of the outer syntax sugar like so:

let
//This is the column with team Ids
    Source = 
        Json.Document(
            Web.Contents("https://api.dashpivot.com/", 
                    [
                        RelativePath = "companies/projects/teams/all",
                        Headers=[
                            Authorization="JWT xxxxxxx"
                            ]
                    ]
                )
                ),
    Source1 = Source{0},
    projects = Source1[projects],
    #"Converted to Table" = Table.FromList(projects, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teams"}, {"Column1.teams"}),
    #"Expanded Column1.teams" = Table.ExpandListColumn(#"Expanded Column1", "Column1.teams"),
    #"Expanded Column1.teams1" = Table.ExpandRecordColumn(#"Expanded Column1.teams", "Column1.teams", {"id"}, {"id"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.teams1",{{"id", type text}}),
    Startdate = Date.AddDays(
                    DateTime.Date(
                        DateTime.AddZone(DateTime.LocalNow(),10)), 
                        -#"Start Days"
                        ),
    Enddate = Date.AddDays(
                    DateTime.Date(
                        DateTime.AddZone(DateTime.LocalNow(),10)), 
                        #"End Days"
                        ),
//Adding the column with List.Generate to iterate over the IDs
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Data", (x)=>
    
    List.Generate( () =>
       [
      
        Result=
        try Json.Document(
                Web.Contents(
                    "https://api.dashpivot.com/v1/",
                    [
                        RelativePath = "teams/"&x[id]&"/photos?",
                        Query = [
                            limit="120",
                            offset=Number.ToText(OffsetValue),
                            startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
                            endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
                          ],        
                        Headers=[
                            Authorization="JWT xxxxx"
                        ]
                   ]
                )
            )otherwise null, Offset = OffsetValue],
        each not List.IsEmpty([Result]),
        each 
        [
      
        Result=
        try Json.Document(
                Web.Contents(
                    "https://api.dashpivot.com/v1/",
                    [
                        RelativePath = "teams/"&x[id]&"/photos?",
                        Query = [
                            limit="120",
                            offset=Number.ToText([Offset]+120),
                            startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
                            endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
                          ],        
                        Headers=[
                            Authorization="JWT xxxxxx"
                        ]
                   ]
                )
            )otherwise null, Offset = [Offset]+120],
        each [Result]
        )
      )

[MKE]

@ImkeF Thanks a lot. Definitely it was the ambiguity on the AddColumn. Looks like List.Generate() is one of the lazy ones. Your solution worked like magic! I appreciate

[ImkeF]

Hi @MKE ,
that's probably because your webcall returns a record and not a list. Could it be that you have to drill into the body-part instead?:

maybe: [Result][Body] ?

[MKE]

@ImkeF The webcall returns a list of records

[ImkeF]

Hi @MKE ,
what does the error-message say?

... just saw that you've used another "try" after the "otherwise". That's not correct syntax an must be deleted:

[MKE]

The try on otherwise is removed but still same error

[MKE]

Thanks for pointing that out @ImkeF , the error says 

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