cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Data set refresh successful but no data for one table

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?

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Data set refresh successful but no data for one table

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".


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Data set refresh successful but no data for one table

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

Highlighted
Helper II
Helper II

Re: Data set refresh successful but no data for one table

@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

Highlighted
Helper II
Helper II

Re: Data set refresh successful but no data for one table

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.

 

 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors