March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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,
Poonam
Solved! Go to Solution.
Hi @Anonymous
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
Hi @Anonymous
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"
Best Regards
Maggie
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?
Regards,
Poonam
Hi @Anonymous
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
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
Hi @Anonymous
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
Is there a way to automatically change the data source in PBI daily via an equation (for example: add 8340 to the middle number in the link every day. So day 1 would by 8340, day to would automatically change to 16680, etc)?
Thank you!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
90 | |
80 | |
49 |
User | Count |
---|---|
160 | |
145 | |
103 | |
72 | |
55 |