Forum Discussion
Using Bex Query to import data into power bi
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
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 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.
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 DatesCombinedForReadabilityWill 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 FormattedDateCall 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 DatesCombinedForReadabilityOutput:
See if that helps.
Im really gratefull for your prompt response in your below code i get "K420200201" but i need only "K42020002"how to achieve this?
Anonymous,
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.