Visualizations cannot display properly in PBI Service with error message

[v-alq-msft]

Issue:  

Visualizations cannot display properly in Power BI Service, and the report is created by Power BI Desktop September 2020 version or later and connects to the data source via DirectQuery. 

Error:  

Unable to convert an M query in table 'TableName' into a native source query. 

Cause: 

This is a known issue and is on fixing.  

It has been confirmed that the behavior of the Power BI desktop is not synchronized with the Power server by default. It’s a by-design issue that on the Power BI Service side, some queries could not be folded (should also throw error in Power BI Desktop).  

Therefore, all you need to do now is to make Power BI Desktop match Power BI Service. Please follow below steps, not workaround, but check, to validate the scenario is correct: 

Verification steps: 

1. Close all instances of PBI Desktop. 

     2. Add the environment variable PBI_throwOnFoldingFailure and set its value to 1. 

• Open “Edit the system environment variables”. 

• Add a new environment variable “PBI_throwOnFoldingFailure” with the value set to 1 in “User variables for xxx” or “System variables”, and click “OK”. 

    3. Open the PBIX with native query failures and click Refresh in the Desktop. You should see the folding failure in Desktop now.  

       In my scenario, the error step is to filter data with List.Max function, which is not supported in DirectQuery.  

     4. Then you can modify the query transformations to make "Apply changes" succeed. 

Note: you won't see failures in the Power Query Editor. 

My workaround is to use SQL Statements to filter data before loading it: 

Then, the visuals will display properly in Power BI Service. 

You can create a test table in SQL Server like below: 

CREATE DATABASE Test 

USE Tests 
  
CREATE TABLE Product ( 

    SalesDate DATE, 

    Category  VARCHAR(100), 

    Product VARCHAR(100), 

    Sales MONEY, 

    Quantity INT 

) 

INSERT INTO Product VALUES('2018-05-05','Accessories','Carrying Case',9924.60,68) 

INSERT INTO Product VALUES('2018-05-06','Accessories','Tripod',1350.00,18) 

INSERT INTO Product VALUES('2018-05-11','Accessories','Lens Adapter',1147.50,17) 

INSERT INTO Product VALUES('2018-05-05','Accessories','Mini Battery Charger',1056.00,44) 

INSERT INTO Product VALUES('2018-05-06','Accessories','Telephoto Conversion Lens',1380.00,18) 

INSERT INTO Product VALUES('2018-05-06','Accessories','USB Cable',780.00,26) 

INSERT INTO Product VALUES('2018-05-08','Accessories','Budget Movie-Maker',3798.00,9) 

INSERT INTO Product VALUES('2018-05-09','Digital video recorder','Business Videographer',10400.00,13) 

INSERT INTO Product VALUES('2018-05-10','Digital video recorder','Social Videographer',3000.00,60) 

INSERT INTO Product VALUES('2018-05-11','Digital','Advanced Digital',7234.50,39) 

INSERT INTO Product VALUES('2018-05-07','Digital','Compact Digital',10836.00,84) 

INSERT INTO Product VALUES('2018-05-08','Digital','Consumer Digital',2550.00,17) 

INSERT INTO Product VALUES('2018-05-05','Digital','Slim Digital',8357.80,44) 

INSERT INTO Product VALUES('2018-05-09','Digital SLR','SLR Camera 35mm',18530.00,34) 

INSERT INTO Product VALUES('2018-05-07','Digital SLR','SLR Camera',26576.00,88) 

SELECT * FROM Product 

Hope this will help you. 

Author:  Icey Zhang

Reviewer: Ula Huang, Kerry Wang