Combining dynamic date filter in Power Query (import) with date conversion

[Anonymous]

Hi,

I have a Query (SAP HANA) connected to my Power BI as an import. When importing the data, I choose my date span (either from a certain date, between certain dates). I would like to create a dynamic date selection so that every time the report is refreshed, the date selection would update. This is in order for the query to not be so big. 

I have changed the code in the advanced editor from

The {0 means "from".

{Cube.ApplyParameter, "VAR__0REQ_DATE", {0, 20201211, null}}

To this 

{Cube.ApplyParameter, "VAR__0REQ_DATE", {0, Date.AddDays (Date.From (DateTime.LocalNow ()) , -1), null}}

This works, but I get an error because the format is coming back in the incorrect format. I would need the date to be in YYYYMMDD and not in YYYY-MM-DD as below.

I found a way to convert a piece of code to the format I'd like by using this code

Number.ToText(Date.Year([Date]))&Number.ToText(Date.Month([Date]))&Number.ToText(Date.Day([Date]))

But I don't manage to combine the Add.days code with the Date.year code - I get "invalid identifier" when I try.

How do I combine these two codes so I can have a dynamic filter in the right format? Appreciate any help, thanks in advance! 

[Jimmy801 · Accepted solution]

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
    Final

Copy 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

Jimmy

[Jimmy801 · Accepted solution]

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
    Final

Copy 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

Jimmy

[Anonymous]

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. 

[Jimmy801]

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

[Anonymous]

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: 

[Jimmy801 · Accepted solution]

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

Jimmy

[Anonymous]

Hi @Jimmy801 , you're right, there was something wrong with my code. I re-did it and now it works. Thanks again!

[Anonymous]

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.