Dynamic data retrieval based on today's date

[oslosa]

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?

[v-juanli-msft]

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]

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())))
,"]"})}}}

[ImkeF]

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.

[oslosa]

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

[artemus]

What locale are you using.

If you create a new blank query with:

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

what is the ouput?

[oslosa]

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 · Accepted solution]

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

yyyy0MM

[oslosa]

Yes, that is what I tried to use.

[artemus]

How about trying:

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

Edit: lower case y

[Greg_Deckler]

@ImkeF