Change data type in Added Items part

[CBergsma]

Hello everyone:

I've a tricky question, for some reason the view that i'm fetching data from it's having a date for Febraury 29th of 2021 (like this year would have a Leap Day), obviously this gives an error since it can convert a date that does not exist.

We are working with SAP HANA database and the error is the following:

DataSource.Error: ODBC: SUCCESS_WITH_INFO [S1000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;1 general warning: currency/unit conversion finished with warnings
could not convert value '29.02.2021' to longdate because value '29.02.2021' is not a valid date

I open the advanced editor and found out that we're converting this into a date in a step following the added items, but the error is happening in the Added Items sentence. The field is DIANAT 

Is there any way to convert DIANAT to a string in the Added Items sentence, or any other way to avoid this error? (Our customer is trying to get the data arranged but we have a report that hasn't been running for 2 days because of this)

#"Added Items" = Cube.Transform(ZAV_VENTAS1,
{
{Cube.AddAndExpandDimensionColumn, "[ZAV_VENTAS]", {"[AREACO].[AREACO].Attribute", "[CORTE1].[CORTE1].Attribute", "[CORTE2].[CORTE2].Attribute", "[DIANAT].[DIANAT].Attribute", "[DIVISI].[DIVISI].Attribute", "[GRPCLI].[GRPCLI].Attribute", "[GRPMAT].[GRPMAT].Attribute", "[GRPVEN].[GRPVEN].Attribute", "[INTERCO].[INTERCO].Attribute", "[MARCA].[MARCA].Attribute", "[MATERI].[MATERI].Attribute", "[SOLICI].[SOLICI].Attribute", "[TAREACO].[TAREACO].Attribute", "[TCORTE1].[TCORTE1].Attribute", "[TCORTE2].[TCORTE2].Attribute", "[TDIVISI].[TDIVISI].Attribute", "[TGRPCLI].[TGRPCLI].Attribute", "[TGRPMAT].[TGRPMAT].Attribute", "[TGRPVEN].[TGRPVEN].Attribute", "[TMATERI].[TMATERI].Attribute", "[TSOLICI].[TSOLICI].Attribute", "[TUNINEG].[TUNINEG].Attribute", "[UNIDIV].[UNIDIV].Attribute", "[UNINEG].[UNINEG].Attribute"}, {"AREACO", "CORTE1", "CORTE2", "DIANAT", "DIVISI", "GRPCLI", "GRPMAT", "GRPVEN", "INTERCO", "MARCA", "MATERI", "SOLICI", "TAREACO", "TCORTE1", "TCORTE2", "TDIVISI", "TGRPCLI", "TGRPMAT", "TGRPVEN", "TMATERI", "TSOLICI", "TUNINEG", "UNIDIV", "UNINEG"}},
{Cube.AddMeasureColumn, "PINGCLP", "[Measures].[PINGCLP]"},
{Cube.AddMeasureColumn, "PINGUSD", "[Measures].[PINGUSD]"}
}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Items", {{"DIANAT", type date}}, "es-CL")

Sorry if something is not clear, english is not my first language.

Thanks in advance.

[sbatool]

We are currently experiencing similar issue with the refresh of the dataflows. Below is the error:
ODBC: SUCCESS_WITH_INFO [S1000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;1 general warning: currency/unit conversion finished with warnings; could not convert value '2010-0' to longdate because value '2010-0' is not a valid date
We even do not have a date column at all in one of the dataflows, all strings.

The same error message is displayed for all our fact views sourcing from SAP HANA (which is recently moved to Azure). In HANA, we experience no issues in doing the data preview. It only fails when used in a dataflow. 

An update on this issue would be greatly appreciated.

[lbendlin]

No, I mean you need to change it in the view in S4, upstream of Power BI.

[CBergsma]

Well, thanks for your time, we are working with our provider to update the data.

Best Regards.

[Icey]

Hi @CBergsma ,

Does what @lbendlin mentioned solve your problem?

If it does, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[CBergsma]

Hi @Icey 

The answer from lbendlin does not solve the problem (at least not the way i would expected to solved it, in powerbi, it only provides with an alternative to solved it in the DB).

Best Regards.

[lbendlin]

yes, that view in S4 acts like a cube. That's the place where you need to fix it.

[CBergsma]

Could you show me where in Power BI can i do that?

The only steps i've are the Source -> Navigation and then Added Items, is there a formula or something where i can filter, or convert the type of data of that specific field?

Thanks in advance.

[lbendlin]

Power Query has a concept of "try - otherwise" for error handling  but that will not help you much as it nullifies the entire step.  Is there no way for you to catch that obviously wrong date already in the SSAS cube?

[CBergsma]

Sorry it took me so long to answer, we don't have a SSAS cube, we're trying to read directly from a view created in SAP Hana. We're already talking with the data provider to solve the wrong date, but we are trying to find a meanwhile solution so we can run the report again.

[lbendlin]

That is not an error, it is an informational message. Is the date dropped/replaced by null ?

[CBergsma]

Hi lbendlin

No, it's not replaced by anything just gives me the message and doesn't show any data, not even on the preview while reading the view on HANA.

Also doesn't let me pass beyond the Added Items Step.