Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pedro503
Resolver I
Resolver I

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

 

Pedro503_4-1687377084784.png

 

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:

Pedro503_1-1687375783221.png

 

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

Pedro503_3-1687376557593.png

 

When I tried using REPLACE on my query, a different error appears:

Pedro503_2-1687376298395.png

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.

2 REPLIES 2
amitchandak
Super User
Super User

@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

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.