Power BI Report not showing data after refresh in Power BI service

[reports_lover95]

Hi everyone,

I am facing very weird issue with Power BI service. It occurs only after data refresh.

I created report using Power BI desktop which is based on some calculations and filtering in Power Query. Everything works fine, I can refresh data as much times as I want and all data arrives as expected.

After publishing to Power BI service report also looks as it should but just after first online refresh part of the data gets away.

After deep analysis I spotted one interesting correlation - when I use some kind of date filter expressions in Power Query then I loose exact data after refresh in Power BI service. Adding example of my current date filter expression:

(Table.SelectRows(#"Filtered budget", each ([effective_from] = Date.StartOfMonth(Date.AddDays( Date.From(DateTime.FixedLocalNow()),-1)) ))

What I tried from recommended online solutions so far:

1. Changing formats from text to date for all date columns in the project
2. Changing locale from local to EN-GB for all date columns in the project
3. Changing locale from local to EN-GB in PowerBI settings
4. Changing locale from local to EN-GB for whole PC

But that didn't work.

So now I am literally stuck without any other options. Maybe some one experienced the same problem also and found a way how to bypass it?

Regards,

I.

[reports_lover95 · Accepted solution]

After a lot of analysis finally I managed to find a bug!

It is very weird behaviour but look how it was caused:

1. We had this SQL code:

Here we can see that all date columns are without assigned data type what means date is assigned by default from Postgresql.

2. I got an idea to try assign data types. Just random guess, so I wrote code like that:

3. From logical perspective result should be the same because Postgresql database assign date types even if we don't add specified format. But in this case Power BI service somehow didn't understand automatic format from Postgresql and cut all data instantly after refresh while Power BI desktop understood that operation and kept all data after any amount of refreshes I did.

In conclusion:

Power BI service doesn't understand automatically assigned formats by Postgresql so we need to add data types by ourselves every time we call date values from database.

[Tutu_in_YYC]

Thanks for sharing!

[SaiTejaTalasila]

Hi @reports_lover95 ,

Recently one of our user have the same issue.You can check column quality of date/time columns and replace null values with some date incase if you any null values.please check your transformations once on every date/time columns.

I hope this steps will help to fix the issue.

Thanks,

Sai Teja 

[Tutu_in_YYC]

When it runs DateTime.FixedLocalNow(), it could be in UTC, ie you might get time zone issues. Have you considered that? 

[reports_lover95]

---

@Tutu_in_YYC wrote:

When it runs DateTime.FixedLocalNow(), it could be in UTC, ie you might get time zone issues. Have you considered that? 

---

Hi,

Of course it was considered and extra tests done with only filtered exact values: #date(2024, 6, 1), #date(2024, 5, 15) and etc.

After this try everything was the same - no data after refresh in PBI service for same tables where date columns were filtered.

[Tutu_in_YYC]

Based on the filter,  you want a specific date ie. [Effectivedate] = Today - 1 day.

Try running:
[effective_from] < Date.StartOfMonth(Date.AddDays( Date.From(DateTime.FixedLocalNow()),-1)) ))

If data comes through, it could mean you transformation is fine, and the issue could be data.

Also create another query that return just:
Date.StartOfMonth(Date.AddDays( Date.From(DateTime.FixedLocalNow()),-1)) ))

SO you can confirm exactly what date is being used to filter.