Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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 

 

Need Help

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.