Really bizarre issue with SQL function not returning result in Desktop but does on service!!!!

[masplin]

I had a model that was working perfectly well and then our servers moved to Azure. this caused some issues with some custom SQL functions that calculated duration.  These were fixed  and if I run the query below in Microsoft Server Management it works fine generating the DurationSecInWorkinghours column

select
   'TEST' AS [Client],
   noteid,
   [Type],
 
   CaseNumber,
   CreatedDateTime,
   FinalisedDateTime,
   FinalisedByAdviserID,
   t.TopicTitle,
   [ARGOS_DW_REPORTING].[dbo].[udf_GetWorkingTime](CreatedDateTime, FinalisedDateTime) AS DurationSecInWorkingHours
   from [ARGOS_DW_REPORTING].[dbo].[Fact_Cases_Notes] fcn
   JOIN [ARGOS_DW_REPORTING].dbo.Dim_Topic t    on t.TopicKey = fcn.TopicKey
   where FinalisedDateTime >= '20200810' 
   and FinalisedByAdviserID <> - 1
   and PostedToCaseViewerFlag <> 'T'
   AND COALESCE(AskTheExpertID, 0) = 0

However when I put this into aquery in Power BI it produces zeros for some but not all clients (we have seperate DB for each client).

let
    Source = Sql.Database("Test.com", "master", [Query="
  select
   'TEST' AS [Client],
   noteid,
   [Type],
 
   CaseNumber,
   CreatedDateTime,
   FinalisedDateTime,
   FinalisedByAdviserID,
   t.TopicTitle,
   [ARGOS_DW_REPORTING].[dbo].[udf_GetWorkingTime](CreatedDateTime, FinalisedDateTime) AS DurationSecInWorkingHours
   from [ARGOS_DW_REPORTING].[dbo].[Fact_Cases_Notes] fcn
   JOIN [ARGOS_DW_REPORTING].dbo.Dim_Topic t    on t.TopicKey = fcn.TopicKey
   where FinalisedDateTime >= '20200810' 
   and FinalisedByAdviserID <> - 1
   and PostedToCaseViewerFlag <> 'T'
   AND COALESCE(AskTheExpertID, 0) = 0


    "])
in
    Source

On the TEST DB I have 5 clients so code above just joined with UNION ALL.  4 of them have results in desktop, but one doesn't. However if I publsih it out all 5 are OK!!!  We have second server TEST2 and on this one there are 12 clients and none of them have anything but zeros and none of them work when published (the query works in MSMS). 

The M code is identical to the code on the pre-Azure servers just the server name changed.   So questions

1. Any ideas what would cause the query to return the results in MSMS but not in desktop. I am using the same windows login?
2. What would cause the desktop model to have a zero result, but for the result to be correct when refreshed on the service (for that one client that does this)?

    One issue could be that each client has a differnet definition of working hours so each function is a bit different.  Even so I'm baffled why MsMS can run it and desktop cant and also why the serve can run it and desktop cant.  We are out of ideas and compeltely crippled our Power BI

Thanks for any advice

Mike

[MFelix]

Hi @masplin ,

Perfect person to answer this questions is @ImkeF the best super user on M language here.

@ImkeF  can you sort this thing out?

[ImkeF]

Hi @masplin ,

nothing super obvious comes to my mind here. Just that the SQL-code looks a bit rough in general.

I would start rebuilding the SQL-query with native Power Query steps, check the auto-generated M-Code (view native query) and replace your SQL-code with it subsequently.

Do this step by step until PBID hits the error. That should identify the delinquent query element(s).