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

Using Bex Query to import data into power bi

Hi Team,

@lmf232s 

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 .

start&end.PNG

Can anyone please give suggestion for the same.

 

Regards,

Husna

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Using Bex Query to import data into power bi

Hi @lmf232s 

 

You are truely a saver.....

I will be often connecting with you hereafter...

I would like to appreciate you alot . Thank you 

 

Regards,

Husna

View solution in original post

9 REPLIES 9
Highlighted
Resolver II
Resolver II

Re: Using Bex Query to import data into power bi

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. 

Dynamic Parameters.jpg

 

 

 

Let me knwo if you have any issues or problems. 

 

lmf232s

Highlighted
Resolver I
Resolver I

Re: Using Bex Query to import data into power bi

Hi @lmf232s 

 

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

date2.PNG

date1.PNG

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 

 

date5.png

 

Regards,

Husna

Highlighted
Resolver II
Resolver II

Re: Using Bex Query to import data into power bi

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. 

Highlighted
Resolver II
Resolver II

Re: Using Bex Query to import data into power bi

I appolgize, you can actually keep the #" in all the fields and it works just fine. I guess I've just never done it that way. 

 

I think it may have something to do with the way you are creating the data. Date's in SAP are typically in an integer format '20200101' rather than a true date '01/01/2020', etc. 

 

Can you do me a favor and connect to the query selecting dates from the paramter drop down and then past your M-Code. This will allow you to see the format of the parameters that you need to pass in. For example, if I just connect to the query and select the dates from the drop downs I get the following;

 {Cube.ApplyParameter, "[0I_DAYS]", {"[ZSD_IS02___F13].[20200101]", "[ZSD_IS02___F13].[20200201]"},

This allows me to see how we need to format the value that is being passed into the parameter. In my original post we were dealing with Fiscal Period so the values was actually '[0FISCPER].[K1220180001]' and in this case you have to make sure that you are passing in the parameter correctly. You'll have to include the 'K12' part of the value and then append the date to this value. 

 

Does that make sense? 

Highlighted
Resolver I
Resolver I

Re: Using Bex Query to import data into power bi

@lmf232s  Thank you , Appreciate your assistance in the below

Finally im about to achieve what i needed but need your help here to get the data for previous month , since current month will always be incomplete, i need start date as 

2/1/2020

  and end date as 

2/29/2020

 

Though i have done some coding but im not sure if it will give me what i needed, since EndofMonth is throwing error like cant take two parameters.

date4.PNG

 

Highlighted
Resolver II
Resolver II

Re: Using Bex Query to import data into power bi

So there are bunch of different ways to do this. I've given you two examples and tried to break them down to the basics. I'm sure there are some other cleaver ways to pull this off but this is what I have for now. 

DateFormatExample

let
    CurrentDate = DateTime.LocalNow(),
    PreviousPeriod = Date.AddMonths(CurrentDate, -1),
    StartOfMonth = Date.StartOfMonth(PreviousPeriod), 
    EndOfMonth = Date.EndOfMonth(PreviousPeriod),
    Year = Date.Year(PreviousPeriod),
    Month = Date.Month(PreviousPeriod),
    FirstDayOfMonth = Date.Day(StartOfMonth),
    LastDayOfMonth = Date.Day(EndOfMonth),
    SAPStartDate = Date.ToText(#date(Year, Month, FirstDayOfMonth), "yyyyMMdd"),
    SAPEndDate = Date.ToText(#date(Year, Month, LastDayOfMonth), "yyyyMMdd"),
    pStartDate = Text.Combine({"[0FISCPER].[K4", SAPStartDate, "]"}),
    PEndDate   = Text.Combine({"[0FISCPER].[K4", SAPEndDate, "]"}),
    DatesCombinedForReadability = Text.Combine({pStartDate, " - ", PEndDate})
in
    DatesCombinedForReadability

Will Produce the following

DateFormatExamples.jpg

 

DateFormatExamples w/Function Function

Create a function

(UserDate as datetime, FormatString as text) => 
let
    Year = Date.Year(UserDate),
    Month = Date.Month(UserDate),
    Day = Date.Day(UserDate),
    DateToText = DateTime.ToText(UserDate, "yyyyMMdd"),
    FormattedDate = Text.Replace(FormatString, "@", DateToText) 
in
    FormattedDate

Call the function

let
    CurrentDate = DateTime.LocalNow(),
    PreviousPeriod = Date.AddMonths(CurrentDate, -1),
    StartOfMonth = Date.StartOfMonth(PreviousPeriod),
    EndOfMonth = Date.EndOfMonth(PreviousPeriod),
    pStartDate = fnCreateDateParameter(StartOfMonth, "[0FISCPER].[K4@]"),
    pEndDate   = fnCreateDateParameter(EndOfMonth,"[0FISCPER].[K4@]"),
    DatesCombinedForReadability = Text.Combine({pStartDate, " - ", pEndDate})
in
    DatesCombinedForReadability

Output:

DateFormatExamplesWithFunction.jpg

 

See if that helps. 

Highlighted
Resolver I
Resolver I

Re: Using Bex Query to import data into power bi

Im really gratefull for your prompt response in your below code i get "K420200201" but i need only "K42020002"how to achieve this?

 

 

Highlighted
Resolver II
Resolver II

Re: Using Bex Query to import data into power bi

@HusnaBanu413

 

Have you tried playing with the code that I posted above. The code above will do what you need it do to, you simply need to exclude the day part and concatonate the year + month. Because your dealing with fiscaly period you'll have to append a 0 to the front of your month so you get 002 insetaed of 02 but other than that everything you need is posted in the code above. 


Give it a shot and if you have a specific issue post the code and we'll see what we can do. 

Highlighted
Resolver I
Resolver I

Re: Using Bex Query to import data into power bi

Hi @lmf232s 

 

You are truely a saver.....

I will be often connecting with you hereafter...

I would like to appreciate you alot . Thank you 

 

Regards,

Husna

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors