Cost Management- Connection

[Sar_Aan]

I am working on a powerBi report and I pulled data from Azure cost management. I tried loading the usage details amortized data into powerquery and I got this.

Expression.Error: We cannot convert the value null to type Record.

Details:

Value= Type=[Type

I have tried modifying the M query from the advanced editor but I am not getting it right.

This is the  M query code

let
Source = AzureCostManagement.Tables("Manually Input Scope", "/providers/Microsoft.Billing/billingAccounts/79XXX250", 1, [startDate=null, endDate=null]),
usagedetailsamortized = Source{[Key="usagedetailsamortized"]}[Data]
in
usagedetailsamortized

[Anonymous]

Hi, @Sar_Aan 

In the M query code, you're trying to access the data in the record. However, the key doesn't seem to exist in the record, or it's associated with a value.

I made some modifications, checked if the key was present in the record, and checked if the key was not associated with a value. If both conditions are true, it accesses the data; Otherwise, it will return

let
    Source = AzureCostManagement.Tables("Manually Input Scope", "/providers/Microsoft.Billing/billingAccounts/79XXX250", 1, [startDate=null, endDate=null]),
    usagedetailsamortized = if Record.HasFields(Source, "usagedetailsamortized") and Source{[Key="usagedetailsamortized"]}[Data] <> null then Source{[Key="usagedetailsamortized"]}[Data] else null
in
    usagedetailsamortized

My modifications may not be quite right. You can follow this line of thought and revise it again

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Sar_Aan]

Thank You,

But ,now the error changed to: 

Expression.Error: We cannot convert a value of type Table to type Record.
Details:
Value=[Table]
Type=[Type]