Unable to use Value.NativeQuery M function in Power BI Report Server January 2022

[andreasolsen228]

Hi MS

My simplest explanation of the problem is that my querying of a SQL Server table via Value.NativeQuery in Microsoft Power BI Desktop (Optimized for Report Server) is not working.

I cannot upload from inside the application (Error when uploading the report) nor can I upload from the Report Server itself (There was an error uploading your .pbix file. Verify that the file has not been corrupted and that the file extension matches the format of the file.)

Both the Desktop and Server versions are of the latest release February 23rd 2022 - 15.0.1108.159

When I change the query from Value.NativeQuery to Sql.Database then it works with both upload methods.

[andreasolsen228]

I found out a fix.

_____________________________________________________________

It seems that the error occurs when I set up my Power Query as such:

1st query (Server):

let
Source = Sql.Databases("ServerName")
in
Source

2nd query (Database):

let
Source = Sql.Databases(Server),
Database = Source{[Name="DatabaseName"]}[Data]
in
Database 

3rd Query (actual query)

let
Source = Value.NativeQuery ( Database, "select * from Database.Dim.Date ")
in
Source

____________________________________________________

If I do this instead, the error does not occur:

1st query (Database):

let
Source = Sql.Databases("ServerName"),
Database = Source{[Name="DatabaseName"]}[Data]
in
Database

2nd Query (actual query)

let
Source = Value.NativeQuery ( Database, "select * from Database.Dim.Date ")
in
Source

______________________________________________________

Bear in mind that the three-stepped approach worked prior to January 2022. 

[Anonymous]

Hi @andreasolsen228 ,

Glad you solved the problem and thanks for sharing.

Best Regards,
Community Support Team _ Caitlyn