Data set refresh successful but no data for one table

[JimJim]

Hi Guys, I have a report that refreshed successfully but doesn't pull in any data for one table. If I refresh the same report (with the same datasource) in desktop I can see the data. The only way to resolve the issue is to delete the dataset and publish the report. As soon as I refresh it again I get a load of blanks. It doesn't matter if it's a scheduled refresh or manual.

The table that's failing to refresh is the biggest table with 32 columns and about 315k rows, could the size be an issue? What are my options for troubleshooting this?

[v-gizhi-msft]

Hi,

I think this issue is related to the too large data.

Maybe it is a refresh timeout issue.

See this:

https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios#scheduled-refres... 

Hope this helps.

Best Regards,

Giotto Zhi

[JimJim]

@v-gizhi-msftI don't think it's a timeout issue, it says that pro users have a timeout of 5 hours and the query completes in about 3 minutes.

@Greg_DecklerI will go down the microsoft support route if I can't get it sorted this week, I checked the issue forum but couldn't see it. I have also attached the advanced editor code you requested. Could it be a gateway issue?

let
    Source = Sql.Database("Sales", "FE1", [Query="SELECT#(lf)    *,#(lf)    CASE#(tab)  WHEN a.[WIP Days] <= 2 THEN '< 2 Days'#(lf)#(tab)#(tab)  WHEN a.[WIP Days] > 2 THEN '> 2 Days' #(lf)    END [WIP Status]#(lf)FROM (#(lf)SELECT#(lf)sa.ServiceAppointmentId,#(lf)sa.ServiceResourceId,#(lf)sa.ServiceTerritoryId,#(lf)sa.ServiceAppointmentStatusId,#(lf)sa.WorkOrderId,#(lf)ISNULL(CAST(CAST(CAST(sa.ActualStartTime AS date) AS datetime) AS int),-1) StartDateId,#(lf)ISNULL(CAST(CAST(CAST(sa.ActualEndTime AS date) AS datetime) AS int),-1) EndDateId,#(lf)sa.WorkTypeId,#(lf)sa.AssetId,#(lf)sa.AppointmentNumber [Appointment Number],#(lf)sa.DueDate [Due Date],#(lf)sa.ActualStartTime [Actual Start Time],#(lf)sa.ActualEndTime [Actual End Time],#(lf)sa.SchedStartTime [Scheduled Start],#(lf)sa.SchedEndTime [Scheduled End],#(lf)sa.EarliestStartTime [Earliest Start],#(lf)CASE WHEN sa.IsMultiDay = 0 AND sas.StatusName IN ('In Transit', 'In Progress') THEN datediff(day,sa.ActualStartTime,CAST(getdate() AS date)) ELSE NULL END [WIP Days],#(lf)CASE WHEN sa.IsMultiDay = 0 AND sas.StatusName IN ('In Transit', 'In Progress') THEN 1 ELSE 0 END IsWIP,#(lf)cu.CustomerName [Customer],#(lf)c.CaseNumber [Case Number],#(lf)wo.WorkOrderNumber [WO Number],#(lf)wo.AgreementNumber [Agreement],#(lf)sa.ActualDuration [Actual Duration (mins)],#(lf)iif(sa.DurationType = 'Hours', sa.Duration*60, sa.Duration) [Scheduled Duration (mins)],#(lf)sa.SalespersonId SchedulerId,#(lf)sa.FSMAsset Asset,#(lf)sa.WorkOrderLineItemId,#(lf)woli.IssueType [Issue Type],#(lf)woli.Source,#(lf)woli.ActionTaken [Action Taken],#(lf)woli.Symptom#(lf)FROM ser.ServiceAppointment sa#(lf)LEFT JOIN ser.ServiceAppointmentStatus sas ON sas.ServiceAppointmentStatusId = sa.ServiceAppointmentStatusId#(lf)LEFT JOIN ser.WorkOrder wo on wo.WorkOrderId = sa.WorkOrderId#(lf)LEFT JOIN ser.Case c on c.CaseId = sa.CaseId#(lf)LEFT JOIN sal.Customer cu on cu.CustomerId = sa.CustomerId#(lf)LEFT JOIN ser.WorkOrderLineItem woli on woli.WorkOrderLineItemId = sa.WorkOrderLineItemId#(lf)WHERE sa.ServiceTerritoryId > -1  #(lf)AND IsMultiDay = 0 AND StatusName IN ('In Transit', 'In Progress', 'Completed', 'Not Complete', 'Dispatched', 'Not Complete - Cancelled', 'Scheduled')#(lf)AND (   sa.ActualStartTime >= DATEADD(YEAR,DATEDIFF(year,0,GETDATE())-1,0)#(lf)    OR  sa.ActualEndTime >= DATEADD(YEAR,DATEDIFF(year,0,GETDATE())-1,0)#(lf)    )#(lf)    ) a#(lf)#(lf)"])
in
    Source

[JimJim]

Guys, just want to add a further update to this which may be relevant.

I manually refreshed the dataset today, it completed without error within about 20 seconds so straight away I suspected that something wasn't right as it usually takes a few minutes. This time the report had no blank data but it also hadn't refreshed (even thought it told me at the top of the report it had been refreshed today). I refreshed the data in desktop, published the report and overwrote the existing dataset which fixed the issue.

[Greg_Deckler]

Doubtful, those are pretty tame stats. Very difficult to troubleshoot in the forums but pasting your Advanced Editor code would be a start.

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".