OData Feed - Key matched more than one row

[avdw]

Hi all,

I'm developing an integration between a shopping platform and PowerBI - by creating an OData feed off the shop data. I have an 'Orders' table which is pulled from this OData feed into PowerBI. Each row contains data on an order, with the 'id' column being the unique key for each. 

There are some nested items for each order, such as for example the 'billing_address' record. This is an object that contains fields for the address (street, city, country,... etc). 

I'm able to pull the OData feed into PowerQuery - and have verified that each row has a unique ID. If I try to access the billing_address 'Record' - I get the following error message: "The key matched more than one row in the table."

However - if I insert a step in the Query where I change the type of the 'id' column (which is already set as Whole Number) to 'Text', 'Whole Number' or even 'Decimal Number', I can access the billing_address Record without any error messages...

Any clues what may cause this issue?

Thanks!

[v-jianboli-msft · Accepted solution]

Hi @avdw 

There is a setting "Automatic detection of column data type and headers" in Power Query.

This setting is specifically for unstructured sources. It helps you by automatically inspecting and detecting column types and headers based on the first 200 rows of your table. When this setting is enabled, Power Query automatically adds two steps to your query:

• Promote column headers: Promotes the first row of the table to be the column header.
• Changed type: Converts the values from the Any data type to a data type based on the inspection of the values from each column.

By default, this setting is enabled. According to your description, maybe this problem is caused by automatic detection of column data type. Automatically detected data type cause errors in data structure recognition.

Best Regards,

Jianbo Li

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