Dynamic loading

[Anonymous]

I am loading my data from Oracle Databse. However, I have a certain conditions for data load and go to edit queries before doing that.  Now, I want my data load to happen such that the Date period is only till July 31 2018. However, on the 4th of every month this needs to dynamically change. For example, on September 4th, data needs to automatically refresh as of August 31 and on October 4th the data has to refresh as of September 30 (Basically end of previous month on every 4th of next month) and my load should not take thoe values between end of 1st- 3rd of next month

Is there any way this can be done? Thank you!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

Maybe you can add one more step to your last solution. Find the boundary of date period first. 

let
    Boundary = if Date.Day(DateTime.LocalNow()) > 3 then Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)) else Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -2)),
    #"Extracted Date" = Date.From(Boundary)
in
    #"Extracted Date"

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

I don't know how you did it. But you can try it like below. You have to edit it in the Advanced Editor.

let
    Boundary = if Date.Day(DateTime.LocalNow()) > 3 then Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)) else Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -2)),
    Boundary_In_Date = Date.From(Boundary),
    Source = Oracle.Database("Dale2016.fareast.corp.microsoft.com/orcl.fareast.corp.microsoft.com", [HierarchicalNavigation=true, Query="select * from hr.employees where hire_date < to_date('" & Text.From(Boundary_In_Date) & "', 'mm/dd/yyyy')"])
in
    Source

Best Regards,

Dale

[Anonymous]

It pops up an error and points towards the word "Boundary"

[v-jiascu-msft]

Hi @Anonymous,

As we can see from my last post, I tested it successfully before posting. So please share some snapshots of yours, including the codes in the Advanced Editor and the error messages.

Best Regards,

Dale

[Anonymous]

@v-jiascu-msft Hey where do I add this part?

[Anonymous]

@Anonymous query editor.