Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello There!
Currently I am stuck on a problem with my data source and couldn't find much on this type of issue without utilizng something like power automate. I am just currently a "small fry" in my company trying to prove my knowledge and sadly, I don't have the true access as this time to utilize automate so I am in search of a workaround for now.
Any help or guidance would be wonderful, even something like "You're thinking about this the wrong way"(Along with a new way to think about it of course ;P) would be truly helpful.
Right now, I have reports that are automatically pulled and saved to the same location everyday it's just that the file path has a date in it so it changes.
Ex, "Basic\Maintenance\15AUG2023\AnotherFolder\OneLastFolder\Destination.csv"
"Basic\Maintenance\14AUG2023\AnotherFolder\OneLastFolder\Destination.csv"
"Basic\Maintenance\13AUG2023\AnotherFolder\OneLastFolder\Destination.csv"
I am currently just using this variable to grab yesterday's date and insert it into the source's file path
YesterDate = Text.Upper(Date.ToText(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -1), "ddMMMyyyy")),
Here is the layout of what I am currently utilizing
let
YesterDate = Text.Upper(Date.ToText(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -1), "ddMMMyyyy"))
Source = Folder.Files("C:\Basic\Maintenance\"&YesterDate&"\AnotherFolder\OneLastFolder\Destination.csv")
in
Source
This works for the most part, except when no one is around on the weekends to pull the reports and then we have to change the source to Friday's and Saturday's reports.
I was hoping for something where the end user could input a date. Say, Aug 13, 2023 and have the variable use that but it doesn't seem to be possible, Unless I am wrong?
I was also thinking of a second and third excel workbook/sheet with the values changed for dates (As much as I prefer not to, as I am thinking there is a better way)
Friday = Text.Upper(Date.ToText(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -3), "ddMMMyyyy"))
Saturday = Text.Upper(Date.ToText(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -2), "ddMMMyyyy"))
Thank you 🙂
Solved! Go to Solution.
You can have a cell in Excel into which the user places the variable, then use Get Data "From Table". Name that Query "DateVar". Then in your path, replace your "13AUG2023" with &DateVar&
Now your user can add the variable in the cell and hit Refresh All.
--Nate
You can have a cell in Excel into which the user places the variable, then use Get Data "From Table". Name that Query "DateVar". Then in your path, replace your "13AUG2023" with &DateVar&
Now your user can add the variable in the cell and hit Refresh All.
--Nate