M Query Error

[Razorbx13]

I am getting the below error in the PowerBI Service and also in any Desktop report created against the dataset.  If I am in the desktop using the PBIX file that created the dataset everything works fine.  I can create visuals, refresh data, work in Power Query, etc.  However, on the service or in a PBIX file connected to the dataset in the service I get the below error even though it is the same visual.  I tried everything and do not know why the PBIX file with the dataset works as it always has, but everything else connected to the dataset (whether a report in the desktop, Excel file, or my existing reports) now have errors.

BTW, I can go into the service and refresh with no problems.

[Fowmy]

@Razorbx13 

Is your source data connected using Direct Query?
Try to remove the steps after Source Step and try if it works. 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Razorbx13]

Well the PBIX file in which the dataset is created has no errors.  I can create reports in the PBIX with no errors.  I can refresh data in the PBIX with no errors.  Once I publish the dataset and then go to the PowerBI service and open the report that just worked in the desktop dataset PBIX I get the error.  The dataset refreshes fine in the service but no reports can be created.  I get the error.  Cannot Analyze in Excel, get a similar error.  

I have seen the message in Power Query when trying to do something not allowed with Direct Query, and I am not getting it.  I know I can troubleshoot and fin out what is going on but again, the dataset refreshes fine and I can create a desktop report fine within the dataset PBIX report.

Just have never seen anything like this.  Normally it fails everywhere. 

[v-janeyg-msft]

Hi, @Razorbx13 

It’s my pleasure to answer for you.

According to your description,You can refresh successfully in the PBI service, indicating that the connection between the gateway and the data source is fine, but this error occurs, I think maybe one step of query is too complicated.

You should know that use DQ has many limitations. One thing is: If the Query Editor query is overly complex, an error occurs. To remedy the error, either delete the problematic step in Query Editor, or import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there's no Query Editor.

Have you tried to delete steps or use import mode to determine the problem? As for the error that you can't use this dataset to create reports and analyze in excel, if it only occurs on this dataset and other datasets are fine, I think you need to fix this problem first.

Reference:Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

Using DirectQuery in Power BI - Power BI | Microsoft Docs

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[Razorbx13]

I was able to remove some items and in now works.  Do not know what changed in the service over the last few weeks that caused everything to stop working.

[v-janeyg-msft · Accepted solution]

Hi, @Razorbx13 

Sometimes there will be some issues after updating the version. If so, Miscrosoft PBI will fix it as soon as possible.

Best Regards

Janey Guo

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