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
alijalil
Frequent Visitor

Issue in conversion of csv dates in PowerBI

Hi all,

 

I'm attempting to create a line graph using the data from excel saved in CSV format. The issue is that because of the conversion the PBI is reading the data as Jan 1, Jan 2 instead of Jan 1, Feb 1. As as a result my line graph (below) is not showing monthly split. Whats the quickest way to make the fix the dates on PBI? Apologies if this question has already been asked - new to PBI and still getting used to the community. TIA!

 

Incorrect line graph

alijalil_1-1669716324748.png

 

Correct line graph

alijalil_2-1669716414665.png

 

1 ACCEPTED SOLUTION

@v-jingzhang thanks for your response. I havent tried your solution but what worked was using the text to column feature and recreating a custom column for the date function.  

View solution in original post

11 REPLIES 11
alijalil
Frequent Visitor

I have created the calendar table using the CalendarAuto function in Power Query. Here you go!

 

alijalil_0-1669723400720.png

 

alijalil
Frequent Visitor

Yes, I did. Pls see beloe the data points.

 

Actual data

alijalil_0-1669722211613.png

 

Budget data

alijalil_1-1669722256682.png

 

Forecast data

alijalil_2-1669722324666.png

 

One more thing, I noticed that my model looks a bit different from the sample solution model. Could this be the reason.

 

Sample

alijalil_4-1669722520917.png

My version

alijalil_5-1669722555930.png

Really appreciate all your help and guidance, much appreciated!

Hi @alijalil 

 

Currently your dates are not recognized with the correct Locale format. You need to go to Power Query Editor, remove the auto-generated "Changed Type" step from "Applied Steps" pane, then change data types of all columns manually. For these date columns, change them to date type with locale English (United Kingdom). This will make Power Query recognize the correct day and month values. After that, apply the queries to Power BI Desktop and try dragging a date column to a table visual to check whether the date values are correct. Ensuring date values are correct is the first step before everything. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

@v-jingzhang thanks for your response. I havent tried your solution but what worked was using the text to column feature and recreating a custom column for the date function.  

What is the date format on the calendar table?

alijalil
Frequent Visitor

I double checked this - as you can see even after the locale changes in your solution the numbers are still in the same format i.e. Jan 1, Jan 2, and so on.

 

alijalil_0-1669721403034.png

 

Did you use united kingdom? Can you show how is the data in the CSV?

alijalil
Frequent Visitor

@JorgePinho  is this what you are after? Many thanks!

alijalil_0-1669716780264.png

 

In Power Query go to go the column of the Date and to this:

Animation.gif

I believe that is the problem!

@JorgePinho Thank you for the suggested solution. I have updated the columns for each of the data points, (forecast, actual and budget) however the issue still persists!

 

As you can see in below screen shot, when I drill down it still only shows the years and the numbers are significantly higher than the correct file shared in my initial post.

alijalil_0-1669719223339.png

 

JorgePinho
Solution Sage
Solution Sage

Can you show an example of your data in Power BI?

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.