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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to convert an M query in table '[Dataset Table]' into a native source query.

Dear Team,

 

Looking for help the the error that couldn't load the data for this visual

Error prompt "Unable to convert an M query in table '[Dataset Table]' into a native source query."

 

No issue while running through the Power Bi Desktop, issue happen when view from Power Bi Web.

The query method is direct query.

 

The connect show successful and database is online in the gateway management.

Thansk.

 

Best regards,

Lawrence

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks @v-eqin-msft , i have went through the post you share, however the issue seem different.

Just for the info, the power bi vis is working in web for sometime, just recently (ermm, around 2 to 3 weeks ago..) not function. Just afraid the recent upgrade for power bi will impact. Any other suggestion?

 

Just to highlighten,

1) no issue for the Desktop base, the issue only happen during the web base after publish to web.

2) gateway setting is successful and connected.

 

wschuah_0-1608520854695.png

 

I reinstall again the Powerbigateway again today, it work now.. Roots cause unknown.

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

Hi Team, in my case the error was originated by the date table. I created the table with a

 

Dates = CALENDARAUTO (12)

 

and then some calculated columns like:

Month = MONTH('Date'[Date])
Month name = FORMAT('Date'[Date], "MMM")
Year = YEAR('Date'[Date])


The datasources created BEFORE the data table were created without any error.
But... any datasource added to the model AFTER the date table was created, caused this error to happen. So the "solution", if it can be called like that, was to remove the date table and then add the datasource, and then again add the date table at the end. I hope this helps anyone because it was a big headache until I figured this out. Can anyone explain this or give some hint about how to avoid the error without deleting the data table?

 

 

Anonymous
Not applicable

Hey! Has anyone had this issue again or found any specific ways to solve it?? I am having it now and looking for ways to resolve it. 

chross
Advocate III
Advocate III

Hey all..  Yesterday, we logged a ticket with Microsoft Premier Support and spoke with them about the "Unable to convert an M Query in table....." issue.  The issue we are all experiencing within DirectQuery mode where our Replace Values steps are causing the visual failures in the Service is a known issue.  Rest assured it nothing any of us did.  They said they are working on this.  They heard an earful from us after we explained our frustration on how our existing reports have been in Production for a long time and now all of the sudden our existing steps (Replace Values) are causing issues.  

Cbutler
Helper III
Helper III

Hi all, 
I was able to resolve this, it was a "replace values" step that was causing the issue. I removed these and did a work around of adding in a calculated column to replace the values. This fixed the issue for me. 

I have logged a ticket with Microsoft (when I couldn’t resolve the issue) and now have asked the question if anything has changed. Will update if they come back to me on this. 

chross
Advocate III
Advocate III

Same is happening for us.  The Report has been live in the PBI Service for over a year and now all of the sudden all my visuals are apeearing as "X"s and when I click on "See details" I get the same error as you guys

v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please provide some screenshots about detailed error message.

 

I also found the similar post which might be helpful to solve your issue, please kindly refer to:

rror: Unable to convert an M query...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,
Eyelyn Qin

Anonymous
Not applicable

Thanks @v-eqin-msft , i have went through the post you share, however the issue seem different.

Just for the info, the power bi vis is working in web for sometime, just recently (ermm, around 2 to 3 weeks ago..) not function. Just afraid the recent upgrade for power bi will impact. Any other suggestion?

 

Just to highlighten,

1) no issue for the Desktop base, the issue only happen during the web base after publish to web.

2) gateway setting is successful and connected.

 

wschuah_0-1608520854695.png

 

I reinstall again the Powerbigateway again today, it work now.. Roots cause unknown.

Anonymous
Not applicable

This error seems to be a wide net that catches a lot of M compiler type problems, it seems.  Anyway, I did this and it gave relief:

  1. Using the Pbi query editor...
  2. Disable load on any/all data sets. Save.
  3. Enable load on any/all data sets. Save.

Try again.

I want to mark this as solution... But I am not sure.. How to do that

Worked for me!!

I had to recreate a few measures again after reloading, hopefully I had a recent backup of those measures.

 

Make sure to Save after disable. Maybe it is obvious, but if you just disable and enable without saving nothing happens.

hi @Anonymous , aplogies for the basic question, I can not find where to disable/enable to load in query editor. Can you provide some steps please. 

Thanks 🙂

Anonymous
Not applicable

In the Power Bi query editor where you have all the heavy tools for transforming etc... Simply right click on the the data source (should be on the left side of the screen) and un/choose Enable load.    

Thanks, found it. Didn't work for me though 😞

khregs
Advocate II
Advocate II

Same issue on my side as well.

In my case, we've traced the failure to a Replace Values step in the M query. The replace was on a datetime field that was replacing certain undesirable data with nulls. 

Anonymous
Not applicable

I am in same boat as @Cbutler .

Cbutler
Helper III
Helper III

Not able to offer a solution here but I am getting the same error message and started yesterday too. 😞 

Anonymous
Not applicable

My issue have been resolved. But not sure the root cause. I remain all the code for my powerbi report. but reinstall and re-setup the gateway. the issue gone.

 

the gateway installation that i use as below. i believe can download from microsoft website.

Filename: PowerBIGatewayInstaller.exe

File version: 14.16.6584.1

 

Hope able to help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.