Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 fro...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-juanli-msft,

       

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

       

      Regards,

      Poonam

    • doniyellin's avatar
      doniyellin
      Regular Visitor
      6 years ago

      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!