Cannot convert the literal to the expected type 'Edm.Decimal'

[Anonymous]

Hi!

I’m building a PowerBI report using data from Azure DevOps OData.

Here is the query:

let
Source = OData.Feed (https://analytics.dev.azure.com/{org}/{proj}/_odata/v3.0-preview/WorkItems?
& "$apply=filter("
& "State eq 'Done'"
& "and startswith(Area/AreaPath,'somearea\subarea')"
& "and (WorkItemType eq 'Support Request' or WorkItemType eq 'Product Backlog Item')"
& ")"
& "/compute(year(CreatedDate) as Year, month(CreatedDate) as Month)"
& "/groupby ("
& "(Area/AreaPath, WorkItemType, Year, Month),"
& "aggregate (CycleTimeDays with average as CycleTimeAvg, LeadTimeDays with average as LeadTimeAvg)"
& ")"
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source

OData URL works fine from the browser window - it returns all expected data.

But running this query from PowerBI Desktop resuls in error on one of the records:

DataSource.Error: OData: Cannot convert the literal '8.675E-06' to the expected type 'Edm.Decimal'.
Details:
    DataSourceKind=OData
    DataSourcePath=https://analytics.dev.azure.com/{org}/{proj}/_odata/v3.0-preview/WorkItems

Here is the raw data returned by OData endpoint:

{"@odata.id":null,"Month":8,"Year":2020,"WorkItemType":"Product Backlog Item","LeadTimeAvg":3.762890575,"CycleTimeAvg":8.675E-06,"Area":{"@odata.id":null,"AreaPath":"somearea"}}

Obviousely, PowerBI Desktop fails to interpred this value: "CycleTimeAvg":8.675E-06

Anybody knows how to fix that?

[qihuiyu]

How did you solve your problem?

[Anonymous]

Afaik it is still not fixed in PBI.
As a workaround try to filter the results so PBI won't receive too small values in data. Like CycleTimeAvg > 0.05.

[v-lionel-msft]

Hi @Anonymous ,

Please refer to the link.

Dealing with ‘Cannot convert a value to target type ‘Edm.Decimal’ Error in Power Apps PCF Control | The CRM Chap

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Maybe I'm missing something but how does the articele about type conversions issue in Power Apps component framework relates to type conversion issue in Power Query OData Feed connector?