cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
barryHD
Regular Visitor

Unable to convert an M query in table [table name] into a native source query

Good evening,

 

So we started having an issue with some reports that had been working fine up until today.  We're getting the infamous - "Unable to convert an M query in table [table name] into a native source query." error.

 

Here's are the details:

Activity ID: 986f3f45-7990-4dd0-adea-cc87ce3e4d13
Request ID: 4772d6a9-e3aa-771f-ec44-0a0ab795e224
Correlation ID: 77b7cc56-2e01-c333-9170-3caf959b0d5c
Time: Mon Dec 14 2020 21:12:35 GMT-0500 (Eastern Standard Time)
Service version: 13.0.14905.74
Client version: 2011.5.04066-train
Cluster URI: https://WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: 986f3f45-7990-4dd0-adea-cc87ce3e4d13
Request ID: 4772d6a9-e3aa-771f-ec44-0a0ab795e224
Time: Mon Dec 14 2020 21:12:35 GMT-0500 (Eastern Standard Time)
Service version: 13.0.14905.74
Client version: 2011.5.04066-train
Cluster URI: https://WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net

 

Any help would be greatly appreciated.

 

Thanks in advance.

1 ACCEPTED SOLUTION
barryHD
Regular Visitor

So the only solution I found was to remove some of the data tranformations (data replacements, removing columns, etc..).  Once I cleared those out it started to work again.  From what I was able to find out from other posts, it sounds like these should not have been able to work in Direct Query mode, but they were working for at least 2 months with no issues.  So even though I was able to get it to work, I don't have a true solution to the issue at the moment.

View solution in original post

14 REPLIES 14
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.  

chross
Advocate III
Advocate III

Same thing just started happening to us.  Our report has been live for over a year and the steps in query editor have not changed. All are in DirectQuery, but now one is causing the issue, therefore we are getting the same message all of you are getting.  We had to unpublish the app so that users could no longer access it why we try to figure out what is causing this.  It's quite frustrating when something that has been working for so long stops working with no explanation.

Razorbx13
Helper V
Helper V

Just an FYI, I had downloaded the 12/16 December release of PowerBI and installed it.  So I went back out this AM and saw there was a 12/19 version.  Downloaded and installed and now I no longer have this error.  Do not know if it was the release or just another install.  But errors are gone.

Razorbx13
Helper V
Helper V

Well that is a solution, but what I want to hear is why there is no issue until today.  In fact, for me it was working at 6am MST as I had an Excel sheet attached to the dataset and it worked.  Now, a few hours later no.  Am getting errors.  So I would like to know what changed and if it will be fixed before I go about making changes to my data models.  My models were fine and no changes were made to those.

barryHD
Regular Visitor

So the only solution I found was to remove some of the data tranformations (data replacements, removing columns, etc..).  Once I cleared those out it started to work again.  From what I was able to find out from other posts, it sounds like these should not have been able to work in Direct Query mode, but they were working for at least 2 months with no issues.  So even though I was able to get it to work, I don't have a true solution to the issue at the moment.

View solution in original post

@barryHD I removed all the Replace Value steps with Conditional Columns and that seemed to work.  It's just baffling to me that the Replace Values steps were in the Direct Queries for over a year and always worked until now. Hmmmm..... Again with the Replace Values steps in the desktop the report was working fine, but no longer was working in the Service.

barryHD
Regular Visitor

@chross  Yeah, that's basically what I ended up doing as well.  Though our Direct Query was only out for two months.  If something was changed on the MS side of things, it would have been nice to have some heads up.

@barryHD It would be nice, but instead we are all left scrambling trying to understand what broke in the process. 

Razorbx13
Helper V
Helper V

I am having the same issues this morning.  Same type of error messages.  Things were working fine last week.

v-easonf-msft
Community Support
Community Support

Hi, @barryHD 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
It will help other community members easily find the solution when they get the similar issue.

Best Regards,
Community Support Team _ Eason

khregs
Advocate I
Advocate I

No help, but I have the same problem with one of my datasets. Error is present with the same dataset in two separate PBI tenants (dev, prod). This dataset was working fine since we last made changes to it on Dec. 4.

 

I've logged a support ticket with MS (120121521005174).

 

Example error:

Activity ID: 8dae5ead-3dee-4e50-9f37-2715d4d4ad78
Request ID: a4cdfa7b-f92b-98d1-c4e2-08ce16441a8e
Correlation ID: ba47fcc6-ddf5-7c95-1580-3189632f634c
Time: Tue Dec 15 2020 11:00:30 GMT-0500 (Eastern Standard Time)
Service version: 13.0.14905.74
Client version: 2011.5.04066-train
Cluster URI: https://WABI-CANADA-CENTRAL-redirect.analysis.windows.net
Activity ID: 8dae5ead-3dee-4e50-9f37-2715d4d4ad78
Request ID: a4cdfa7b-f92b-98d1-c4e2-08ce16441a8e
Time: Tue Dec 15 2020 11:00:30 GMT-0500 (Eastern Standard Time)
Service version: 13.0.14905.74
Client version: 2011.5.04066-train
Cluster URI: https://WABI-CANADA-CENTRAL-redirect.analysis.windows.net

Update in case this helps anyone esle with troubleshooting:

In my case, the query step that caused the error was a Replace Values operation on a datetime field to replace some undesirable data with nulls. It was also legacy step that had no effect because the source database had been updated to remove the undesireable values some time ago. Even so, it was all working just fine until we noticed the failure today. 

Well the only way I had to fix it is to remove steps that was causing it.  Like above it was Replace Values steps.  Once removed it worked.  Do not know what caused this as I had no problems for several months.  This just started showing up.

@Razorbx13 I'm sure we will never know what really caused this. But I'm sure a recent change in the service was the cause.  There is no other explanation if your reports and mine that have been running for over several months without any problems all of the sudden have problems. 😑

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.