The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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
Hello!
Please explain where to insert this query that will fix this problem?
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.
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
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
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.
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?
"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.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |