Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |