OLE DB or ODBC error: [Expression.Error] We cannot convert a value of type Record to type Table

[ShaunBuchan]

Hey, 

when using the advance editor I can see the tables and fields as desired within the query editor but when I click close & apply,

I receive the error:

OLE DB or ODBC error: [Expression.Error] We cannot convert a value of type Record to type Table

Here is the code:

let

    Source = Json.Document(Web.Contents(),

    #"Converted to Table" = Record.ToTable(Source),

    #"Expanded Value"=Table.ExpandListColumn(#"Converted to Table","Value"),

    #"Expanded Value1"=Table.ExpandRecordColumn(#"Expanded Value","Value",{"u_subcategory","number","company","u_service_profile","assignment_group","assigned_to","approval","short_description","active","state","priority","risk","start_date","end_date","type","work_end","work_start","approval_history","approval_set","business_service","category","u_change_type","std_change_producer_version","close_code","closed_at","cmdb_ci","impact","location","opened_at","phase","requested_by","service_offering"},{"u_subcategory","number","company","u_service_profile","assignment_group","assigned_to","approval","short_description","active","state","priority","risk","start_date","end_date","type","work_end","work_start","approval_history","approval_set","business_service","category","u_change_type","std_change_producer_version","close_code","closed_at","cmdb_ci","impact","location","opened_at","phase","requested_by","service_offering"}),

in

    #" Expanded Value1"

any help would be greatly appreciated.

Thanks

[v-shex-msft]

HI @ShaunBuchan ,

Have you tested to refresh your query table to confirm the last version data work with your query steps?

AFAIK, power query will cache old table structure in query steps, if new structure not matched with old table structure, it may return this error message.

In addition, if you can please share a pbix file to test, it is hard to troubleshooting without any detail information and sample data.

How to Get Your Question Answered Quickly  
Regards,

Xiaoxin Sheng