Forum Discussion
Combining dynamic date filter in Power Query (import) with date conversion
Hello Anonymous
Check out this dynamic solution. Use this output as input in your Cube-parameter
let Yesterday = Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1), Year = Text.From(Date.Year(Yesterday)), Month = "0" & Text.From(Date.Month(Yesterday)), Day = "0" & Text.From(Date.Day(Yesterday)), Final = Year & Text.End(Month,2) & Text.End(Day,2) in FinalCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello Anonymous
where is this error caused? Did you input my code into the blank query? And it's the name "Query1"?
If you can answer both questions with yes, then this error is caused by something else. Maybe somewhere else in your code you are using a variable without definition.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Jimmy, thank you for this. So in my query, I need to add this code somewhere in the query (I added it as step 3, after step 1 "Source" and step 2 "Content". And later in my Cube parameter I will exchange "20201211" for "Final" - right? (without the quotation mark on "Final")?
Edit: To be clear, I tried it in a blank query and that works fine, the question is on where to put it in the advanced editor code for the actual query.
Hello Anonymous
you can add this code to a step in your query like
GetYesterdayInSAPFormat = and here my code
and then use the variable GetYesterdayInSAPFormat in your cube-parameter.
However you can also create a new blank query, copy my code and then use the query name to feed your cube-parameter.
I don't know if this parameter takes a text (my output is a text). IN case it's not working you have to change the format to number before passing it to the cube-parameter
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Jimmy801 , thanks a lot! This seems to work. I actually just pasted all four rows so they are added as four separate steps, and then I reference back to "Final" in the cube parameter.
Hi again Jimmy801 . I was trying your second solution as well, meaning creating a blank query and referincing it. How would you suggest that I reference it in the code?
When I reference the new query (Query1)
{Cube.ApplyParameter, "VAR__0REQ_DATE", {0, Query1, null}}I get the following error message:
Hello Anonymous
where is this error caused? Did you input my code into the blank query? And it's the name "Query1"?
If you can answer both questions with yes, then this error is caused by something else. Maybe somewhere else in your code you are using a variable without definition.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy801 , you're right, there was something wrong with my code. I re-did it and now it works. Thanks again!