Forum Discussion

Falcon's avatar
Falcon
Helper I
2 years ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    • Falcon's avatar
      Falcon
      Helper I
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.