ODBC Dataflow Refresh Error

[swang123]

Hi everyone! Having an issue with a dataflow refresh, hoping someone can assist.

We have installed a data gateway on an Azure virstal machine. Data is from an ODBC connection which was refreshing fine until yesterday.

We are now getting an error saying: Error: The key didn't match any rows in the table. Request ID: 4fccdde4-7814-4db9-132e-b3efc428a380 Activity ID: ef9d12c0-6ab0-428d-aef4-d3837749789c

Tried loading the data via ODBC in Power BI desktop directly, and it loads fine. The query editor on powerbi.com states that the query has errors. The query is below:

let
Source = Odbc.DataSource("dsn=Main ODBC"),
shipedge_alpaca_fulfyld1_Database = Source{[Name = "main_alpan1", Kind = "Database"]}[Data],
shipping_out_Table = shipedge_alpaca_fulfyld1_Database{[Name = "shipments_out", Kind = "Table"]}[Data],
#"Inserted Lowercased Text" = Table.AddColumn(shipping_out_Table, "lowercase", each Text.Lower([Carrier]), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Lowercased Text", {{"lowercase", "Carrier_mast"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Carrier_ActualCosts", Currency.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type", {"Carrier_mast"})
in
#"Removed Columns"

Does anyone have any idea as to why this may be happening?

[GilbertQ]

Hi there

Could I suggest you upgrade and install the latest version of the On-Premise Data Gateway and see if that resolves your issue?

[swang123]

Hi,

Thanks! We did update to the latest version (3000.9.194)

[GilbertQ]

Hi there

In the dataflow make sure you click on the triangle where it says there are errors.

It will add in a step which should resolve the error. I have had to do this before.

[JosefPrakljacic]

Hey @swang123 ,

please check if

• Your credentials are still right
• The columns you are using are still in place (columns didn't get deleted or renamed)

I bet someone renamed/deleted a column in your table.

Here are similar threads

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table...

http://community.powerbi.com/t5/Integrations-with-Files-and/Expression-Error-The-key-did-not-match-a...

https://community.powerbi.com/t5/Service/Key-Expression-Error-The-key-didn-t-match-any-rows-in-the-t...

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[swang123]

Hi! Thanks for responding. 

Just checked this out - none of the columns have changed. The Key error only in shows in the PowerBI service website (where we refresh the dataflow). When trying to refresh within the query editor online, it only says that "the query contains errors".

Not sure why this may be happening still?