Can you pass multiple variables in a List.Generate?

[tross4012]

I need to create a variable called [MaxRank], which will scale of variable [offset]. After each iteration, [MaxRank] should be equal to [offset] + 999. How can I implement this into my function?

[offset] and [MaxRank]'s values are converted into text and used for an api call later in the report. I just need to pass this roadblock!

Thanks a ton

[Daryl-Lynch-Bzy]

@tross4012 - I am thinking the output of your original M script would be something like:

{ 1, 1001, 2001, 3001 ... 40001 }

This can be converted to a Table Column.  Then you can just add a new column with Offset + 9999.  Then add another column to determine the Relative Path for you API call.

I might be over-simplifying because I am not sure what the FnGetStudies() function is doing in the List Generate.

[tross4012]

FnGetStudies works like this:

The API requires Min_Rnk and Max_Rnk to have a value, and I can only pull in a 1000 records at a time, so I need my variable [MaxRank] to work based off of [offset]. Does that help explain what I'm trying to accomplish or just make things more confusing? 

Really appreciate your help nonetheless.

[Daryl-Lynch-Bzy]

@tross4012  - Is this what you are trying to get?

let
    Source = List.Generate( () => [offset = 0, getstudies = 1] , each [offset] <= 41000, each [offset = [offset] + 1000, getstudies = [offset] ], each [getstudies] ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Min"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Min] <> 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Max", each [Min] + 999),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Min", type text}, {"Max", type text}}),
    #"Add URL" = Table.AddColumn(#"Changed Type", "Add Url", each "https://ClinicalTrials.gov/api/query/study_fields?&fields=NCTId,LeadSponsorName,Phase,BriefTitle,OrgStudyID,OverallStatus&fmt=csv&max_rnk=" & [Max] & "&min_rnk=" & [Min], type text),
    #"Get Data" = Table.AddColumn(#"Add URL", "Csv Data", each Table.PromoteHeaders( Table.Skip( Csv.Document(Web.Contents([Add Url]),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),10))),
    #"Table Append" = Table.Combine( #"Get Data"[Csv Data] )
in
    #"Table Append"

[tross4012]

This is perfect Daryl!!!! The last thing is that I need to not hardcode the [offset] to 41,0000. Is there anyway to make this variable and have it pull in the very last rank the API generates?

At the begginning of each get, it even provides the number we need:

"NStudiesAvail: 412259"
"NStudiesFound: 412259"

How can I make that 41,000 scale off the NStudiesFound? 412,259 records is a lot of data. Even hardcoding that in place of 41,000 causes the REST API to timeout.  How can I handle this? 

Thank you so much for your assistance!

[Daryl-Lynch-Bzy]

Hi @tross4012 are you going to end up with the following table?

Row	Offset	MaxRank
0	1	1000
1	1001	2000
2	2001	3000

[tross4012]

That's what I'm aiming to do however the code snippet in my original post does not accomplish this without the user manually entering the [MaxRank].