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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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.

 

p4dd4_0-1607677113989.png

 

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! 

 

 

2 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
Community Champion

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

Jimmy801_0-1607679427383.png

 

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

View solution in original post

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

View solution in original post

7 REPLIES 7
Jimmy801
Community Champion
Community Champion

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

Jimmy801_0-1607679427383.png

 

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
Not applicable

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

Anonymous
Not applicable

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: 

p4dd4_0-1607694001904.png

 

 

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
Not applicable

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

Anonymous
Not applicable

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. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors