Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
willpage
Helper II
Helper II

Column mismatch between Desktop and Service

Hello,

 

I'm relatively new to Power BI so hopefully this is something super easy and obvious to the more experienced.

I have a basic report that contains a single line chart visual. The data source is a SharePoint list that contains a couple of columns, one of which is a date and the other (Title) is a number.

My visual simply plots how the number changes over time, with a new item being added to the SP every day and the dataset refreshed via a Logic App.

The date column is obviously UTC because it's a SharePoint list, so in Power BI I added a new column like this:

 

 

NZ Date = FORMAT('Stale Tickets Counter'[ReportDate]+TIME(13,0,0),"dddd dd/mm")

 

 

 

The data looks like this:

image.png

And the visual looks like this:

image.png

 

Ok, all good so far, so I publish to the Power BI service and everything is good.

 

..Until, the next time a new item is added to the SP list and the dataset is refershed via the Logic App.

 

image.png

The first item in the list, which has a date of 12th Jan UTC, converted to 13th Jan NZST by adding 13 hours, has become the 2nd of December, and been sorted to the end of the chart.

If I look at the data from the model in Power BI service:

image.png

Only that first one, the 12th Jan UTC has been interpreted as US date format by switching the day and month round then adding 13 hours, but the rest haven't.

 

Now, if I go back to Power BI Desktop, click refresh to include the latest data, then, without touching the model at all, re-plublish to the PBI service, the problem goes away.. until tomorrow!

 

Any insights on this would be greatly appreciated!

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi there

What I would suggest doing is to rather create the new column in the Query Editor.

Then create a new column and add in the following code:
[ReportDate] + #duration(0,13,0,0)




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

Proud to be a Super User!







Power BI Blog

View solution in original post

5 REPLIES 5
GilbertQ
Super User
Super User

Hi there

What I would suggest doing is to rather create the new column in the Query Editor.

Then create a new column and add in the following code:
[ReportDate] + #duration(0,13,0,0)




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

Proud to be a Super User!







Power BI Blog

@GilbertQ  It's been running a few days now with no problem with the date reoccurring. Thanks for your help!

Hi there

Awesome thanks for letting me know and everyone else know that it is now working!




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

Proud to be a Super User!







Power BI Blog

@GilbertQ  Thanks for your reply. I'm still very much unaware of what the various use cases and merits are of power query vs DAX. There seems to a lot that can be done in both equally.

 

Anyway, because I want to format the date I did this:

DateTime.ToText( [ReportDate] + #duration(0,13,0,0),"dddd dd/MM"))

 

I wasn't sure whether I would be better adding the column in query editor like you describe and then adding another in the report to format the date, but I chose this way for now.

 

I've also changed the sort column to the ID of the SharePoint list item so the visual will always be in chronological order even if the date gets mucked up.

 

I'll find out tomorrow when the refresh happens if this has worked. Thanks again.

Hi there

Yeah I know it can be a challenge on where to decide where to do what.

I typically do all my data manipulation in Power Query because it is a lot easier. I have also found that it always works well and I do not have any weird things happening either.

Let us know if this works!




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

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors