Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
oslosa
Helper I
Helper I

Dynamic data retrieval based on today's date

Hi, I am trying to retrieve data for the most recent date from an SAP BW server. It requires me to query a parameter in the following format "[0FISCPER].[K4202003]" to retrieve March 2020 data.

 

When using the following:

{Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4",Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())),"]"})}}}

I get the error message:

Expression.Error: We cannot convert the value 202003 to type Text.

Details:

    Value=202003

    Type=[Type]

which indicates that I am on the right track, but as expected I need it to be a string and not a number.

 

I then try to convert it to a string with Text.From(), but then I get the error message:

DataSource.Error: The SAP BW server reported an error: 'Value '03 .2020' is not valid

which is not at all the '202003' I expected. Does anyone understand why I don't get the desired conversion?

 

1 ACCEPTED SOLUTION
artemus
Microsoft Employee
Microsoft Employee

oh, the solution I gave can easily be fixed... just add a 0 e.g.

yyyy0MM

View solution in original post

10 REPLIES 10
v-juanli-msft
Community Support
Community Support

Hi @oslosa 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
 
Best Regards
Maggie
ImkeF
Super User
Super User

HI just to be specific here:

This is delivering the error-message you've posted below?:

 

{Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4",
Text.From(Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())))
,"]"})}}}

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hm, actually a bit at a loss here - how can this formula actually work?

Wouldn't it have to be:

 

{Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4" &
Text.From(Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())))
 
& "]"})}}}

 

Please check your exact coding again.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Of course, the error was that I should have multiplied by 1000! I wanted 2020003 and not 202003

artemus
Microsoft Employee
Microsoft Employee

What locale are you using.

 

If you create a new blank query with:

= Text.From(<<Your logic>>)

what is the ouput?

It was the error message from the SAP server that was confusing. I thought that my output from Text.From() was '03.2020', which didn't make any sense, but that was just SAP's interpretation. I got '202003', which was expected given the code, but I needed '2020003' with the extra zero for it to be a valid entry in SAP. Your proposed solution would have worked were it not for the extra zero.

 

Anyways, case closed. Thanks for the fast responses guys.

artemus
Microsoft Employee
Microsoft Employee

oh, the solution I gave can easily be fixed... just add a 0 e.g.

yyyy0MM

Yes, that is what I tried to use.

artemus
Microsoft Employee
Microsoft Employee

How about trying:

 

DateTimeZone.ToText(DateTimeZone.FixedUtcNow(), "yyyyMM", "en-us")

 

Edit: lower case y

 

Greg_Deckler
Super User
Super User

@ImkeF 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors