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 filte...
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

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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 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:

       

      See if that helps. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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