Using List.Generate(), and when the list has just one record

[Anonymous]

Hi All,

I am using List.Generate() to retrieve data from Web API’s ( Web.Contents() ). As the returned the data is paginated with a dynamic URL (for NextPageURI), I am looping through the calls until I reach the end. I.e. NextPageURI=”N/A”

All was working fine until I realized there could be possible data missing. So I ran the same logic, for just 1 single page of data (adding a date filter) and confirmed that it doesn’t work. No data is returned back.

Sample Code –

Let

    DataList = List.Generate(

                    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?daterange=2019-09-13%202019-09-13&version=7",ImportedXML=""],

                    each Text.PositionOf([SourceURI],"N/A") = -1 and [ImportedXML] <> null,

                    each [

        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=XXXX&user_token=XXXX"""]]),

        ImportedXML = Xml.Tables(Source,null,1252),

        ChangeType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text}, {"NextPageURI", type text}}),

        SourceURI = Record.Field(Table.First(ChangeType),"NextPageURI"),

        TimeEntries = ChangeType{0}[TimeEntries],

        TimeEntry = TimeEntries{0}[TimeEntry]

        ],

    each [[SourceURI],[ImportedXML]]

    )

in

    DataList

The result I get back is –

Seems like the loop is exiting before it can set ImportedXML and the first record in the list has ImportedXML = “”

Would appreciate, if someone could point out where I 

[ImkeF]

Not sure I can fully follow, but you have to adjust/play around with the second argument a bit. How about so for a start?:

each (Text.PositionOf([SourceURI],"N/A") = -1 and [ImportedXML] <> null) or [ImportedXML] = "",

f

[Anonymous]

Thank you, @ImkeF.

I tried playing around with the condition argument. But it doesn't work. Here is the code snippet again.

let
     
    DataList = List.Generate(
                    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?daterange=2019-09-13%202019-09-13&version=7",ImportedXML=""],
                    each Text.PositionOf([SourceURI],"N/A") = -1 or [ImportedXML] = "",
                    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=XXXX&user_token=XXXX"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangeType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text}, {"NextPageURI", type text}}),
        SourceURI = Record.Field(Table.First(ChangeType),"NextPageURI"),
        TimeEntries = ChangeType{0}[TimeEntries],
        TimeEntry = TimeEntries{0}[TimeEntry]
        
        ],
    each [[SourceURI],[ImportedXML]]
    ),
    DataList1 = DataList{0}
in
    DataList1

I am sure there is data in the response. However, I cannot get it in "ImportedXML". The above code returns only a single page of info.

If I broaden the date range and get back multiple pages, I always end up with n-1 pages. I.e, I always miss 1 page of information.