PBI doesn't load the right date from Power Query

[Anonymous]

Hi all,

In my Power Query I have a project showing the Date as 01/08/2020 after all modifications (first parsing date (because somehow it avoided some date errors with other datetime columns) and later transformed it to Date). That date is fine. But If I go to the Table view, the same project shows as 31/07/2020. The data base is up to date.

The original date is 2020-08-01T00:00:00+01:00, then I have two steps resulting in 01/08/2020, but in desktop the result is 31/07/2020. Why? Here the two steps in PQuery.

1) = Table.TransformColumns(#"Expanded PreawardLiaison1",{{"FunderDeadline", each Date.From(DateTimeZone.From(_)), type date}, {"BidSubmittedDate", each Date.From(DateTimeZone.From(_)), type date}, {"BidAwardedDate", each Date.From(DateTimeZone.From(_)), type date}}). Gives as result 01/08/2020.

2) = Table.TransformColumnTypes(#"Renamed Columns",{{"ProjectID", Int64.Type}, {"ProjectTitle", type text}, {"Status", type text}, {"ProjectType", type text}, {"Funder", type text}, {"HESAFunderCategory", type text}, {"NameofCall", type text}, {"FunderDeadline", type date}, {"BidSubmittedDate", type date}, {"BidAwardedDate", type date}, {"ProjectLead.Login", type text}, {"ProjectLead.Display", type text}, {"CoInvestigators.Login", type text}, {"CoInvestigators.Display", type text}, {"LeadOrganisation", type text}, {"Collaborators", type text}, {"Subawardees", type text}, {"Partners", type text}, {"OtherOrganisations", type text}, {"LeadOrgUnit", type text}, {"Themes", type text}, {"ResearchCentresGroups", type text}, {"PreAwardLiaison", type text}, {"BusinessLiaison", type text}, {"PricetoFunderexPartnerCosts", type number}, {"LeadSubproject", type text}, {"OrgUnit", type text}, {"PricetoFunder", type number}}). Keeps the result as 01/08/2020.

Thanks.

[Anonymous · Accepted solution]

Hi, finally I found the problem.

The problem was that the data published on PBI Service didn't refresh properly the date (refreshing every day). 

So, I did download the lastest PBIX file from the PBI Service, refreshed the data on PBI Desktop, and the date updated. Then I published back the whole data again to PBI Service.

[v-yiruan-msft]

Hi @Anonymous ,

Please review the solution in the following threads with same problem, hope they can help you resolve the problem.

Table content different in Query Editor and Table View

Why I get different data in Power Query and Table View for the same field?

Difference between data in Query Editor and Data View

Best Regards

[Anonymous]

Sorry, didn't help. I updated cache and have latest version. Any guidance?

[DataZoe]

@Anonymous I find having dates with the timezone part can lead to unwanted results sometimes. Have you tried removing the timezone and working with it instead just as a datetime?

[Anonymous]

Hi,

Thanks for your response. Is not what I did in the first step? In that step, the resulting value is 01/08/2020 with no time or zone. Or maybe you refer to something else?

Thanks.