Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
5 years ago
Solved

Dataset Refresh error - Null Value

hi,

 

Since upgraded to Gateway version Sep, 2020. I got this error:

 

  • Underlying error code-2147467259
  • Underlying error messageMicrosoft SQL: Warning: Null value is eliminated by an aggregate or other SET operation.

 

I tried to:

SET ANSI_WARNINGS OFF
GO

however, it is not working.

Any suggestions please?

Need Help
  • Iamnvt's avatar
    Iamnvt
    5 years ago

    hi,

     

    Seems like some updates from query folding. I pushed back some of the operations when creating VIEW in SQL server, instead of using query folding feature in Power BI, and it works again.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Probably you are doing a count over a column that contains null values.

    Try replacing you SQL statement with 

    SELECT COUNT(ISNULL(<fieldname>,0)) if you want to count NULL rows

    OR

    SELECT SUM(CASE WHEN <fieldname> IS NULL,1,0) if you do not want to count

  • Iamnvt's avatar
    Iamnvt
    Continued Contributor
    5 years ago

    I dont have any count operations, and it was good with previous Gateway.

    Only since I upgraded to version Sep, 2020 (or afterward), it occurred the issue.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Iamnvt,

    If you download the pbix file and test on the desktop side, did this issue appears again?
    According to your error message, It seems like you are work with SQL data source, any advanced t-SQL statement append in the connector? I'd like to suggest you use SQL profiler to trace the requests to confirm if any issue appears in it.

    BTW, you can also take a look at the following link about similar issues if it helps to your scenario:

    Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio 

    Regards,

    Xiaoxin Sheng

  • Iamnvt's avatar
    Iamnvt
    Continued Contributor
    5 years ago

    hi,

     

    Seems like some updates from query folding. I pushed back some of the operations when creating VIEW in SQL server, instead of using query folding feature in Power BI, and it works again.