Error Publishing Report : Internal Server Error 500

[shaunwilks]

I am going back and fourth with support on this who just wish to give me workarounds for a bug that was introduced in January BI Service update. 

Its simple.  The PBIX has 3 parameters, SQL Server, Database Name, Code.

The "Code" parameter allows the query to be filtered by the specific code being entered as a parameter.

If the parameter is not supplied then the query returns ALL records in the table.

The query is as simple as...

let
Source = Sql.Database(#"Microsoft SQL Server Name", #"Tencia Database Name",
if #"Code" <> Null
then
[Query="SELECT * FROM TABLE WHERE CODE = '" & #"Code" & "'", CreateNavigationProperties=false]
else
[Query="SELECT * FROM TABLE", CreateNavigationProperties=false])
in
Source

This works perfectly well in the PBIX. Data is returned to the report accordingly. It has worked in all our report content for 3 years.

HOWEVER  Since the January BI Service update you cannot publish the report to the Service.

It does not matter what PBI DEsktop you use (Ive tried 5 seperate versions). You also cannot upload the PBIX.

In both instance you receive an Internal Server Error 500 publishing the report.

The publish hates the line in the query ....... "if #"Code" <> Null

When you look in the error detail and drill down on the issue - the publish doesnt like a query with a Null comparison.

Again there is no good reason why - The M Script is solid and its worked for years. 

Without any changes to our content it was the MS update that caused it not to work.

The support consultant I got on my ticket would rather try and discuss workarounds. Even telling me I should hardcode a value in the "Code" parameter within the query.   Which is stupid and defeats the purpose of providing the report content to clients where we wouldnt even know what the code may or may not be.

So I know there are more smarts in the community. 

Its a clear bug - and I have pointed out the exact line within a query that will trigger it.

MS please resolve this one ASAP because we are stuck unable to publish content (Yet MS are accepting our $$)

[v-chuncz-msft]

@shaunwilks 

You may change if expression as follows.

[Query = if #"Code" <> null

[shaunwilks]

Thay produces syntax errors

I shouldnt have to make any changes. The M script is fine.

PBI desktop already processes it and returns data correcly.

If PBI desktop produces a report, parses the query without error and returns data then surely the PBI Service shold accept that report being published.