Forum Discussion
Problem pulling data out of MySQL in Power Query
Hey there.
I've been trying to pull data out of a MySQL server all day long but did not manage to. What I got so far is a two-column wide table that contains the ticket Id and the values of the ticket on a string (it actually is a JSON file that, at first, has not yet been converted into a table, so it is stored into a string as shown in the following image).
The query is quite simple, just as follows:
= MySQL.Database("ServerName", "DataBaseName", [ReturnSingleDatabase=true, Query="SELECT * FROM DataBaseName.json#(lf)WHERE ID<>""temp"""])
So far so good, but when I try to import this table into my model the following error pops up:
As far as I know, the problem lies on these null values, but even when I replace both on the query and on Power Query I get the above error
When I tried using REPLACE on my query, a different error appears:
SELECT REPLACE(t.json, 'null', 0)
FROM DataBaseName.json tThe previous query actually replaces the null values into 0, however not even that works out at the end of the day.
Important: I'm using the last-available version of MySQL connector (today is 21-June-2023).
Any thoughts?
Thanks in advance.
2 Replies
Anonymous , You can bring in Json in Power Bi and can split that column here in power bi
How to import JSON Data: https://youtu.be/MPYfEetvFH4
In select please check the exact function you want to use, in case you are using custom SQL
Thanks for your response, amitchandak .
I watched your video but I don't think it fits in my problem. Basically in my case I pull the data from a MySQL server, and from there each row has a JSON alike format (in each row there's a string from which I can extract the data from).
However, it's also possible that I did not understand what you meant in your message. In what part should I select the MySQL to bring these JSON files?
Once again, thanks for your response.
