Problem pulling data out of MySQL in Power Query

[Pedro503]

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 t

The 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.

[amitchandak]

@Pedro503 , 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

[Pedro503]

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.