Power Bi service refresh returns blank table

[Hussein_charif]

i have a report published on pbi service with around 10 tables. at a specific scheduled refresh time in the morning, when the scheduled refresh finishes, 1 of those 10 tables which contains the main data values is returned blank, and i would have to either refresh it from the desktop or wait a while before refershing on service for the table to be filled again with data. it is only happening with that 1 table and during that specific scheduled referesh time in the morning. what could be the cause and how could it be fixed?
i checked in power query if i have any type mismatches but there are none specifically in the table with the problem.

[skparavind_1994]

Can you please provide whether data fetching from data flow or directly from server?

[danextian]

Hi @Hussein_charif 

Did you filter on date/datetime using a volatile formula -today(), DateTime.LocalNow, etc?  The services uses UTC so today your timezone maybe different the service's.

[Hussein_charif]

Hi @danextian,

no i dont have any measure using Today() function, basically the table just returns all blanks when i check it in the service, then when i refresh again in service it fills to a certain date, then refersh again, then fills a bit more... until the whole table is filled with the most recent data. could it be something related to the data gateway it is connected to? 

[danextian]

how about filtering from the query itself? Anything that filters a date/datetime column?

[Hussein_charif]

yes there is a date filter, speaking of that when its refereshed the dates are blank and when i try to navigate with the date slicer (it's a between type), the dates that were highlited in black and can be navigated to become unusable, meaning i cannot press on that date again as if it doesnt exist in the table

[danextian · Accepted solution]

If you're able to refresh correctly in desktop but getting no rows in the service depending on when the refreshe is made, then it is a timezone difference between yours and the service's. You need to take into consideration when using volatile functions like today(), now and the M equivalents the timezone difference.

The formula below returns the current utc time and adds 8 hours, +8 being my UTC offset, and then parses the date only.

INT ( UTCNOW () + DIVIDE ( 8, 24 ) )

Below is the M equivalent. Notice the need to remove the timezone prior to parsing the date as there is a bug in the service that still returns the UTC equivalent if TZ is not removed.

Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0)))

[Hussein_charif]

thank you @danextian, i'll work on the scheduled refreshes to make sure they aren't interrupted by a refresh from the server then