Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic loading

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!

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

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"

Dynamic_loading

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

dynamic_loading2

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous query editor.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.