Advance Query - load data from the latest date to current date

[Falcon]

Hello all,

I am trying to load data using API which requires parameters "startDateTime" and "endDateTime" in the request URL. The data table has a date column. My plan is as follows.

• Run a first query to pull data in a certain time range.
• Run the second query (daily) pulling data from the same data source. The "startDateTime" will be the "latest date" from the first table +1, and "endDateTime" will be the current date (which I assign a variable to DateTime.LocalNow().

I am struggling to create a variable "LatestDate" which should be the latest date from the first table. Can anyone help me with this issue? 

Thanks in advance

[Anonymous · Accepted solution]

Hi @Falcon ,

@lbendlin Thanks for your concern about this case!

For "startDateTime", to extract the Maximum Date from Your Table, you can use the following M code to create a new query:

let
    Source = YourTableName,
    MaxDate = Table.Max(Source, "YourDateColumnName")[YourDateColumnName]
in
    MaxDate

For the "endDateTime", you can directly use

DateTime.LocalNow()

in your API request URL or format it as needed.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Falcon]

Thanks a lot. That worked for me.

[lbendlin]

are you familiar with the concept of self referencing semantic models?  That might actually be appropriate for your use case.

[Falcon]

Hello lbendlin,

Sorry I am not familiar with the concept of self referencing semantic model. Can you explain further? 

Thanks for all the help

[lbendlin]

in your Power Query code you can access a SSAS data source, via a DAX query. That includes the semantic model you are sitting in...  So you can theoretically run a DAX query to ask for the max date in the semantic model, and then add all the data points after that date through your API. 

It's brain freeze territory,  so maybe having  csv files with the extracts is easier.