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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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

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.

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

yyyy0MM

Yes, that is what I tried to use.

How about trying:

 

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

 

Edit: lower case y

 

Greg_Deckler
Super User
Super User

@ImkeF 


@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors