I'm pretty new to working with M language, and I need some basic help getting list.generate() to do what I want it to do. Some context: I am currently working with an API that allows download of data in csv format, but they've structured it so that I have to do so in sort of a three step process. I have to call the endpoint 1st to submit a job, 2nd to check if the job is complete, and 3rd to download the report. I'm stuck on the 2nd step. I'm trying to use list.generate as a while loop to say while the job is not yet complete keep calling the endpoint with a delay until you get a successful response. Here's an abbreviated version of my code:
let
SourceURL = "the url",
options = [ SOAPAction="action to submit the job",
#"Content-Type"="text/xml;charset=UTF-8"
],
//this is just paramaterization for values in the envelope
currentTime = DateTimeZone.ToText(DateTimeZone.FixedUtcNow(), [Format="yyyy-MM-ddTHH:mm:ss.FFFZ"]),
laterTime = DateTimeZone.ToText(DateTimeZone.FixedUtcNow() + #duration(0,0,5,0), [Format="yyyy-MM-ddTHH:mm:ss.FFFZ"]),
WebContent = Web.Contents(SourceURL, [Content=Text.ToBinary("
//The whole envelope
"),
Headers = options]),
//This assigns a parameter used in the envelope for the Status check call. credit for expandall function comes from http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
JOBID = ExpandAll(Xml.Tables(WebContent)){2}[Table.Table.Table.Table.Table.JobId],
// the intitial assignment for the conditional variable
JobStatus = Function.InvokeAfter(()=>checkStatus(SourceURL, JOBID), #duration(0,0,0,5)),
//the problem.
Source = List.Generate(() => JobStatus, ()=> JobStatus = "Started", () => JobStatus = Diagnostics.Trace(TraceLevel.Information, JobStatus, Function.InvokeAfter(()=>checkStatus(SourceURL, JOBID), #duration(0,0,0,5))))
in
Source
Here's the code for the checkStatus function:
let checkStatus = (SourceURL as text, JobID as text) =>
let
currentTime = DateTimeZone.ToText(DateTimeZone.FixedUtcNow(), [Format="yyyy-MM-ddTHH:mm:ss.FFFZ"]),
laterTime = DateTimeZone.ToText(DateTimeZone.FixedUtcNow() + #duration(0,0,5,0), [Format="yyyy-MM-ddTHH:mm:ss.FFFZ"]),
options1 =[SOAPAction="action to check if job is complete",
#"Content-Type"="text/xml;charset=UTF-8"
],
Envelope = Text.ToBinary("the envelope"),
WebContent1 = Web.Contents(SourceURL,
[Content = Envelope,
Headers=options1]),
Source1 = ExpandAll(Xml.Tables(WebContent1)){3}[Table.Table.Table.Table.Table.Table.Table.StatusType]
in
Source1
in
checkStatus
Right now my query is returning a list titled "error" with one value in it, likely just the starting value of the variable. What I would want as an output is a list containing every value of JobStatus, and stopping when the last value of the list doesn't equal "Started". It's pretty clear I'm having issues with the syntax of the function, and with function declarations in general. Does anyone know how I can fix this?
