Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
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
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
@Anonymous query editor.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |