Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
oh, the solution I gave can easily be fixed... just add a 0 e.g.
yyyy0MM
Hi @oslosa
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
67 | |
22 | |
18 | |
12 |