cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Create Dynamic parameter to load data in Power BI

Hi,

 

I am very new to Power BI. I have one requirement to load the data for one field from previous year to next 3 years.

So, the filed is 'PO_Plan_Receipt_Year' and it has all years starting from 2011 to 2025.

I want to load data in Power Bi only form previous year to next 3 years. so it should be always :

year(Today())-1 to 'year(Today())+3 i.e (2017 to 2021). I am expacting something like below image.

How I can achieve that?

Thanks!

 

Regards,

PoonamCapture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create Dynamic parameter to load data in Power BI

Hi @patilpoonam21

Please pay attention to the bold character, paste the following code in your Advanced editor.

let
Source = Sql.Database("10.76.62.207", "sagedb"),
dbo_V_OPPORTUNITIES_OIF = Source{[Schema="dbo",Item="V_OPPORTUNITIES_OIF"]}[Data],
#"Replaced Value" = Table.ReplaceValue(dbo_V_OPPORTUNITIES_OIF,"","0.0",Replacer.ReplaceValue,{"OIF_Value_EUR"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"OIF_Value_EUR", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Opp_Key", each [Opportunity_ID]&"-"&Date.ToText([Load_Creation_Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PO_Plan_Receipt _Year", Int64.Type}}),
Startdate=Date.Year(DateTime.LocalNow())-1,
Enddate=Date.Year(DateTime.LocalNow())+3,
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [PO_Plan_Receipt _Year] >= Startdate and [PO_Plan_Receipt _Year] <= Enddate)
in
#"Filtered Rows

 

Best Regards

Maggie

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Create Dynamic parameter to load data in Power BI

Hi @patilpoonam21

Write this code in the Advanced editor.

let
    Source =******,
    #"Changed Type"=****,
    Startdate=Date.Year(DateTime.LocalNow())-1,
    Enddate=Date.Year(DateTime.LocalNow())+3,
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [date] >= Startdate and [date] <= Enddate)
in
    #"Filtered Rows"

11.png

 

Best Regards

Maggie

 

 

Re: Create Dynamic parameter to load data in Power BI

Hi @v-juanli-msft,

 

Thanks for your reply. I tried the above expression but I am getting below error. Can you please help me on that?

Capture.PNG

 

Regards,

Poonam

Community Support Team
Community Support Team

Re: Create Dynamic parameter to load data in Power BI

Hi @patilpoonam21

Could you check the data type of the "date" column? (or the column used before and after the "=<")

In my test, it is of Type Number.

Or could you share the code you used in the Advanced editor so i can analyze where is wrong?

Additionally, how about delete the operator, then re-write it? It seems you use < instead of <=.

 

Best Regards

Maggie

Re: Create Dynamic parameter to load data in Power BI

Hi @v-juanli-msft,

 

Thanks for your reply!

 

I am using below code :

 

let
Source = Sql.Database("10.76.62.207", "sagedb"),
dbo_V_OPPORTUNITIES_OIF = Source{[Schema="dbo",Item="V_OPPORTUNITIES_OIF"]}[Data],
#"Replaced Value" = Table.ReplaceValue(dbo_V_OPPORTUNITIES_OIF,"","0.0",Replacer.ReplaceValue,{"OIF_Value_EUR"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"OIF_Value_EUR", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Opp_Key", each [Opportunity_ID]&"-"&Date.ToText([Load_Creation_Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PO_Plan_Receipt _Year", Int64.Type}}),
Startdate=Date.Year(DateTime.LocalNow())-1,
Enddate=Date.Year(DateTime.LocalNow())+3,
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [PO_Plan_Receipt _Year] >= Startdate and [PO_Plan_Receipt _Year] <= Enddate)
in
#"Filtered Rows

 

The data type of 'StartDate' is 'Any'. The data type of 'PO_Plan_Receipt _Year' was Text. I converted it to Whole number. (You can see the 'Change Type1' script code. I tried using "&StartDate&" but still it is not working. If I use hardcoded value "2017" , it works.

Could you pleas ehelp me on that?

Thanks!

 

Regards,

Poonam

Community Support Team
Community Support Team

Re: Create Dynamic parameter to load data in Power BI

Hi @patilpoonam21

Please pay attention to the bold character, paste the following code in your Advanced editor.

let
Source = Sql.Database("10.76.62.207", "sagedb"),
dbo_V_OPPORTUNITIES_OIF = Source{[Schema="dbo",Item="V_OPPORTUNITIES_OIF"]}[Data],
#"Replaced Value" = Table.ReplaceValue(dbo_V_OPPORTUNITIES_OIF,"","0.0",Replacer.ReplaceValue,{"OIF_Value_EUR"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"OIF_Value_EUR", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Opp_Key", each [Opportunity_ID]&"-"&Date.ToText([Load_Creation_Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PO_Plan_Receipt _Year", Int64.Type}}),
Startdate=Date.Year(DateTime.LocalNow())-1,
Enddate=Date.Year(DateTime.LocalNow())+3,
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [PO_Plan_Receipt _Year] >= Startdate and [PO_Plan_Receipt _Year] <= Enddate)
in
#"Filtered Rows

 

Best Regards

Maggie

View solution in original post

Re: Create Dynamic parameter to load data in Power BI

Hi @v-juanli-msft,

 

Thank you so much for your reply. It is working as expected 🙂

 

Regards,

Poonam

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,112 guests
Please welcome our newest community members: