cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
barryHD
Frequent 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
Frequent 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

19 REPLIES 19
Claudio
Frequent Visitor

Buenas tardes,

 

Desde hace 1 mes estoy teniendo el mismo problema que ustedes estan redactando en el caso.

Mi error es el siguiente:

Error bi.png

Fui siguiendo algunas soluciones que ustedes indican, elimine en Power Query Editor, algunos pasos de conversión, y no funciono.

Al mismo tiempo elimine la tabla de Calendario que tengo creada, eliminando al mismo tiempo todas las medidas que fueron creadas con campos de la tabla Calendario, y funciono correctamente. El error se produce en la tabla "Ejercicio contable", pero eliminando Calendario, funciona. Ahora bien, luego de eliminar la tabla calendario, guarde y cerre la app, abri la app nuevamente y cree la tabla de calendario, pero no tuve la misma suerte que antes...NUNCA ANDUVO, Y SE SIGUE PRODUCIENDO EL MISMO ERROR M.

Me ayudan si tienen otra opción para probar?

tonyburg
New Member

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?

 

 

This was my problem also. Thanks @tonyburg 

nikilouwgmail
Helper V
Helper V

Hi All

I have converted my LiveConnection model to Direct Query and I am gettng this error.

 

I have done numerous incompatibility/DQ Limitations fixes already, but this remains when I open Power BI.

 

Any ideas what could cause it?

I still have some native query generated tables that unions two views in Power Query Editor..


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
Post Patron
Post Patron

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
Post Patron
Post Patron

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

@barryHD @PAPutzback2 @tonyburg @nikilouwgmail @chross 

 

i removed the distinct clause from my table query and it started working for me. Remember to NOT use report level DAX columns inside M query. 

Everything was working for me when my report was connected to SAP HANA but when i changed the source to Snowflake, the report broke.

My DAX column was created using COMBINEVALUES and this also didnt allow combining columns created at report level using DAX.

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

@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
Post Patron
Post Patron

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 II
Advocate II

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors