cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ngkim
Regular Visitor

2 Errors: Unknown column and Key not present in dictionary

 

Hello.

 

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'
Details:
DataSourceKind=MySql
DataSourcePath=localhost;db_energy
Message=Unknown column '$Ordered.?_????' in 'field list'
ErrorCode=-2147467259

 

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.
Details:
DataSourceKind=MySql
DataSourcePath=localhost;db_energy

 

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 9.3.0.4984

MariaDB Connector 1.0.5 (Latest for Windows)

Power BI Desktop 2.31.4280.601

 

9 REPLIES 9
hongjyan
Helper II
Helper II

Hello

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("10.181.165.202", "pobot", [ReturnSingleDatabase=true, Encoding=TextEncoding.Utf8])

 

 

if you interest, go on for my story 🙂

Frist, I googled and got ticket https://community.powerbi.com/t5/Desktop/Display-Import-Mixed-Character-Set/m-p/277909 , from accepted answer of this link, I realized that I should change code at advance editor, quite simple code as following there:

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

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("10.181.165.202", "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

https://social.technet.microsoft.com/Forums/en-US/7b29d033-6230-4fa0-aafc-8313ea771f24/change-encodi... 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("10.181.165.202", "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("10.181.165.202", "pobot", [ReturnSingleDatabase=true, Encoding=TextEncoding.Utf8]).

Bang, it worked.

pqian
Microsoft
Microsoft

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.

 

Capture.PNG

 

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

ngkim
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

 

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

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

 

Pic1_AppliedSteps_Source.png

 

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

 

<Relation among tables>

upstream_world_fields

upstream_world_plays

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.

 

Pic2_AppliedSteps_Navigation_ViewNativeQuery.png

 

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.

 

Screenshot.png

 

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?

Nhallquist
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
Details:
DataSourceKind=MySql
DataSourcePath=localhost;db_energy

 

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

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors