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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors