Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using Bex Query to import data into power bi

Hi Team,

Anonymous 

We have a Bex Query created while importing the data from source we need to fill the filters (start & End Date) . Now the issue is everytime i need to manaually change the filter to see the new data which is tedious , want to avoid manual intervention .

Can anyone please give suggestion for the same.

 

Regards,

Husna

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

     

    You are truely a saver.....

    I will be often connecting with you hereafter...

    I would like to appreciate you alot . Thank you 

     

    Regards,

    Husna

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Take a look at this post. I posted this a long time ago but have followed this pattern since and haven't had any issues. 

    https://community.powerbi.com/t5/Desktop/SAP-BW-BEx-Dynamic-Parameters/m-p/806078#M387707

     

    Here is an image from one of my reports. This has been running for a long time but as I look at it there are some things that stick out. I should probably update the SAPStartDate parameter to take the current year - 1 or 2 (Depending on how far back we want to go). I shouldn't have hard coded the start year like that as the users aren't going back to 2017 at this point. This just causes the file to grow with unnecessary data. Also, on my SAPEnddate I have set it to the current year and hard coded the last day of the year. I dont' have as much as an issue with this as this as there is issue with selecting a date in the future like this. That said, each one of these values could be dynamically created based on today's date and what were trying to accomplish. One last thing, I also prefer to create the paramter as the full field value and pass that in as a value (As shown in the pDateFrom & pDateTo). It's been a while since I've had to play with these but I believe by doing it this way you can still interact with your navigation steps where as if you just pass in the parmeter value into the Cube.ApplyParameter Step I beleive that you have issues interacting with the navigation steps. Any way, hopefully this points you in the right direction. 

     

     

     

    Let me knwo if you have any issues or problems. 

     

    lmf232s

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      I tried replicating your M code , but i get the below error

      For Start and end date i wanted to keep it as previous month, since we get the complete data of the month , i hope i have done the coding correctly for that 

       

       

      Regards,

      Husna

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Change your code to the following (Removing all the # and ")

        CurrentDate = DateTime.LocalNow(),
CurrentMonth = Date.AddMonths(CurrentDate, -1),
Monthex = Number.ToText(Date.Month(CurrentMonth)),

        Then pass in this way

        [Cube.ApplyParameter, "[ZSUSRPER]", {Monthex, Monthex}},

         

        Keep in mind I hand coded this so there may be a mistake that would prevent you from copying the code but you should get the idea. Notice I removed all the # and " from the fields.