Date/Time/Timezone Column is blank when refreshed in PowerBi Service

[wiselyman3]

Hi All, 

I have a report that I created in Desktop and published to Service. After this initial publish, the table visual in Service looks correct with everything loaded. However, when I refrresh the dataset and then refresh the visual, I lose all of the Date/Time/Timezone column data. The column is still there, but with all blank values. I also lose a calculated column (all blanks) that is based off of this date/time/timezone column. All of my other columns are still populated and correct, it is just these date columns affected. 

Is there a reason that the data is fine in Desktop, but on 1st refresh in service I lose the data in that column? 

[Cookistador · Accepted solution]

When you set up your connection 

Here, click on advanced Options, then you have to write the query,

If you know the name of the table, you can make something simple like:

Select *,
CAST(TRANSACTION_TIME AS TIMESTAMP_NTZ) AS Transaction_Time_Clean
FROM TABLENAME

Replace TRANSACTION_TIME  by the name of the column you are facing the issue with the date

[v-dineshya · Accepted solution]

HI @wiselyman3 ,

Thank you for reaching out to the Microsoft Community Forum.

Please check below things to fix the issue.

1. Ensure that the role used in Power BI Service is explicitly defined and has access to the Date/Time/Timezone column. Avoid relying on default roles, especially if using SSO or On-Premises Gateway.

2. If you are using DateTime.LocalNow(), consider replacing them with static values or parameters passed from the source system. Alternatively, use a custom function that adjusts for UTC and daylight saving time.

3. Ensure that the Date/Time/Timezone column is not being implicitly converted to a type that Power BI Service cannot interpret. If you changed it to Text, make sure the transformation is applied consistently across all steps and that the column is not being dropped or overwritten during refresh.

4. If you are using an On-Premises Data Gateway, ensure it’s configured with the correct Snowflake role and that the gateway version supports the features you need AAD SSO.

5. Power BI Desktop may be using your machine locale, while the Service uses the default locale of your tenant. Normalize Formats in Power Query.

Please refer Microsoft article and community threads.

Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

Solved: Time columns blank / empty after publish / refresh - Microsoft Fabric Community

Solved: Re: PBI Service refresh empty columns - Microsoft Fabric Community

Solved: blank dates causing issues in Power BI service - Microsoft Fabric Community

Solved: Blank dates in PowerBI report - Microsoft Fabric Community

Solved: Missing column when refreshing - Microsoft Fabric Community

Solved: DateTimeZone not recognized when using DateTimeZon... - Microsoft Fabric Community

Solved: Today's date not refreshing in Power Bi Service - Microsoft Fabric Community

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @wiselyman3 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @wiselyman3 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @wiselyman3 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[Cookistador]

Thank you for your feedback

What kind of datasources are you using ?

I faced a similar issue with SQL Server and the data gateway so maybe, it is comming from this side

[wiselyman3]

The only data source is a Snowflake database, it's cloud based so only a simple login credential needed to connect, which appears to be working as far as I can tell

[Cookistador]

Ok, the issue is probably comming from Snowflake

nowflake has several timestamp data types, most commonly TIMESTAMP_NTZ (No Time Zone) and TIMESTAMP_LTZ (Local Time Zone). When you connect Power BI to a column with a timezone (LTZ or TZ), the Power BI Service can get confused during the refresh about how to convert that timezone information

So instead of taking a table, can you use a query and converting the date with the following sql command

CAST(TRANSACTION_TIME AS TIMESTAMP_NTZ) AS Transaction_Time_Clean,

[wiselyman3]

I see. Thank you, I am unfortunately a beginner so I'm not quite sure how I would do that. 

[Cookistador · Accepted solution]

When you set up your connection 

Here, click on advanced Options, then you have to write the query,

If you know the name of the table, you can make something simple like:

Select *,
CAST(TRANSACTION_TIME AS TIMESTAMP_NTZ) AS Transaction_Time_Clean
FROM TABLENAME

Replace TRANSACTION_TIME  by the name of the column you are facing the issue with the date

[Cookistador]

Hi @wiselyman3 

The Date/Time/Zone data type can be particularly problematic. While Desktop might correctly infer the timezone from your local settings, the Service can struggle to interpret it consistently during a refresh.

Could you make the following test to check if the issue persists or not

Can you try to change your Date/Time/Timezone into text in Power Query, and then republish the report with another name, after that try to refresh

If the issue is due to that, you will have to split the Date/Time and timezone in 3 columns (also a better approach has you will reduche the cardinality)

[wiselyman3]

Unfortunately I am still getting the same result after changing it to Text. That same column is showing all blank once published and refreshed in Service