Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

2 Errors: Unknown column and Key not present in dictionary




I am trying to connect to my local database. It's MariaDB 10.1.9.

Connection is always successful but I can get the data from only 1 table.

And I get permanently 2 types of errors from all other tables as below.


<Error Type 1>

DataSource.Error: MySQL: Unknown column '$Ordered.?_????' in 'field list'
Message=Unknown column '$Ordered.?_????' in 'field list'


This error comes from 2 tables in my db.

These tables are time series of 1) various interest rates, and 2) currency rates.

All data are in DATE and DOUBLE. Field names are in Korean.


<Error Type 2>

DataSource.Error: The given key was not present in the dictionary.


This error comes from all other tables.

All they are about oil & gas fields and time series of their reserves & production.

Data types are VARCHAR(30~255), BIGINT, and DOUBLE.

Field names are in English.


And all the tables and columns are set with character set of utf16_general_ci.


Could anyone help me please?


p.s. My environment is as below.

Win7 32bit

XAMPP (including MariaDB 10.1.9) + Heidi SQL

MariaDB Connector 1.0.5 (Latest for Windows)

Power BI Desktop 2.31.4280.601


Helper II
Helper II


I met the same problem, fix it by 2 hours google and guess.

In short, adding code below in red&blod in your advance editor and blame MS for their casual document.

MySQL.Database("", "pobot", [ReturnSingleDatabase=true, Encoding=TextEncoding.Utf8])



if you interest, go on for my story 🙂

Frist, I googled and got ticket , from accepted answer of this link, I realized that I should change code at advance editor, quite simple code as following there:

Source = MySQL.Database("", "pobot", [ReturnSingleDatabase=true]),
pobot_fb_issues_final = Source{[Schema="pobot",Item="fb_issues_final"]}[Data]

so, the only possible place need to be updated is powerquery function MySQL.Database, so I checked its function meta-type from mysql-database . where Encoding : A TextEncoding value that specifies the character set used to encode all queries sent to the server (default is null) was known. so seems something like this:

MySQL.Database("", "pobot", [ReturnSingleDatabase=true, Encoding="utf8"])

may work, but unfortunately, it hinted me:

DataSource.Error: MySQL: This function doesn't support the query option 'Encoding' with value '"utf8"'.

Neighter for UTF-8,utf8mb4,...

after serveral failed try, I reminded that encoding may be number from the question of which was menthion by answer of last link. from the second ink, line: How change encoding from 1251 to 65001 (utf-8) posted, so I got that encoding maybe represented by number. So, I tried

MySQL.Database("", "pobot", [ReturnSingleDatabase=true, Encoding=1251].

But it stll hinted me by error:

Expression.Error: The value for a 'TextEncoding.Type' enumeration was not one of the allowed values: 'TextEncoding.Utf8, TextEncoding.Utf16, TextEncoding.Ascii, TextEncoding.Unicode, TextEncoding.BigEndianUnicode, TextEncoding.Windows'.

so, I tried

MySQL.Database("", "pobot", [ReturnSingleDatabase=true, Encoding=TextEncoding.Utf8]).

Bang, it worked.


The best way to report this problem is to use "Send a Frown", make sure you include formula and screenshot for both errors.


Here are some things you can do to diagnose the issue further:


The first error seems like Unicode/Korean encoding issue. Can you get the folded sql statement from the queries editor and post it here? We need to identify whether the encoding is lost in the provider or in the M folding logic (unlikely)


Go into View->Advance Query Editor, and paste the formula here, and then right click the last step in Applied Steps, and click "View Native Query", paste the contents here as well.




The second one is weird, since the navigation is in English. I advise you try clearing the data cache in the options dialog, under Global->Data Load->Clear Cache.

It would also help if you can paste the Query formula here

Regular Visitor

Dear @pqian


Thank you for your reply.

As my previous reply to @Nhallquist, I got it to work somehow.

Now I'm dealing with the "Key not present in the dictionary" error.


Here are the scripts in [Advanced Query Editor] as you mentioned.


Table : upstream_world_fields


    Source = MySQL.Database("localhost", "db_energy", [ReturnSingleDatabase=true]),
    db_energy_upstream_world_fields = Source{[Schema="db_energy",Item="upstream_world_fields"]}[Data]

Power BI Message : No syntax errors have been detected.


And all the tables have exactly same script as above, only with different table names.

Power BI messages are the same about all tables, saying "No syntax errors have been detected."

Here is the table name list




And the `upstream_world_~` tables are related as below.


<Relation among tables>



upstream_world_reservoirs      (2 foreign key constraints, referencing to `~_fields` and `~_plays`)

upstream_world_interests        (1 foreign key constraint, referencing to `~_reservoirs`)

upstream_world_events           (1 foreign key constraint, referencing to `~_interests`)

upstream_world_costs             (1 foreign key constraint, referencing to `~_events`)

upstream_world_reserves2P    (1 foreign key constraint, referencing to `~_costs`)

upstream_world_production     (1 foreign key constraint, referencing to `~_reserves2P`)


About "View Native Query" you mentioned, it was not active for both "Source" and "Navigation" so I couldn't click it.

See the screenshot below.




And finally, clearing cache didn't help it unfortunately.


It sounds like you are still having the same issues with the "Key not present" errors.  Let's start at the beginning.  Can you check the database that you are accessing and make sure that the account you are logged in as, and the account that is running PowerBI both have READ access to the tables and columns?  Not familiar with that database, but this is where I would start, since the script syntax is correct.

Dear @Nhallquist


Thank you for your reply.

I am always logging in database with root account, and Power BI can read all the tables as you can see below.




As my earlier post, I can get the data from the first 3 tables only.



@ngkim this doesn't make any sense...


If you delete everything and just leave this step:

Source = MySQL.Database("localhost", "db_energy", [ReturnSingleDatabase=true])


You see the base navigation table right? Next, if you click on the link inside the table, what happens?


is db_energy your database name AND your schema name?

Helper V
Helper V

"What is the column name that is represented in Error Type 1?  The error shows"$Ordered.?_????".  Maybe there is a character that is not recognized in the column name?  Try renaming that column, and then see if that clears you other error.

Dear @Nhallquist


As mentioned earlier my reply, I retried it after deleting the text fields `DataSource` and `DataQuality` written in Korean.


And finally, it successfully got the data!     (^o^)/


Thank you very much for your advice!

Now I can get the data from those 3 tables of time series.


Now then I will turn to other complicate tables those of various text and numbers data.


Dear @Nhallquist


Thank you for your reply, it partially solved my problem.


I have changed all field names of 2 tables (interest rates and currency rates) into English as your advice.

(All other tables' fields are in English already)


And then Power BI successfully got the data from interest rates table.

But about currency rates table, it gets a new error as below.


<New error from a table where originally I got "Unknown Column" error>

DataSource.Error: Non-negative number required.
Parameter name: count


I suspect it's because this table has 2 text fields in Korean.

Field Names: `DataSource`, `DataQuality`

And all data of these 2 fields are "Bank of Korea", "OFFICIAL(NATIONAL)" (written in Korean)


I will retry after deleting these 2 fields and then see what happens.

Thank you again and I will report you what happens.



Helpful resources

June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors