Date Format Error

[GK600]

Hi,

Hope someone can help me. I have looked through many forums but didn't come across this one.

My regional settings is English (United Kingdom) and Data load type detection is set to below.

My data source is excel file from an application which presents US date format like below.

When I load the data, it appears as text format. 

The date format ideally is the desired one (I want it to stay as US formatted only). So, I thought either of below may help but didn't  😞

1. Simply change the format to Date. When I do it, below error happens. Important thing to note here is that error only comes up for dates after 12th of the month.

2. Or, I click the Date column > using locale > Date type as "Date" and Locale set to "English (US)". Strangely, it rather converts dates into United Kingdom format which is not what I want. See below.

3. Changing my regional setting to English (United States) and then change the format of Date column to "Date". But it still appears in UK format (exactly as in 2nd attempt above).

I would appreciate if someone can look into it and help fixing this. I spent hours already. Any guidance will be appreciated. Thanks. 

Also, please find attached pbix and excel file in use for this.

https://1drv.ms/u/s!Aq2FeLHchvwogoFqQJywpf7U8Y8kKw?e=L8mfDt

regards,

Gaurav 

[AntrikshSharma]

@GK600 The dates in your excel file are actually stored as text, you can tell that by the left alignment of a number. Numbers are always right aligned in excel a quick and dirty way is to do this, since excel is accessible to you. Once you covert them back to numbers using double negative, you can replace the original column with the new one:

[Pragati11]

Hi @AntrikshSharma ,

I don't agree modifying date column from TEXT to NUMBER data-type in excel, because Power BI can easily handle the date values that come as Text through a excel file.

Like see below raw data from excel:

Now I can transform this TExt Date column to date type by using Locale option and it gets converted quite easily:

I used United Sates date format "Using Locale" because I know they are in that format, and Power BI easily converts the text to date value:

Here my default Date format is United Kingdom, therefore dates are shown in this format.

Let me know if I mis-understood your response. 🙂

Thanks,

Pragati

[AntrikshSharma]

Hello @Pragati11 ,

Agreed, but the thing is his dates are in a different format, see the apostrophe before the date, that kind of format isn't changed by using using locale. Can you create same scenario in your system and let me know if it works for you.

[Pragati11]

Hi @AntrikshSharma ,

I don't see apostrophe sign in any of the date values.

I am checking the excel file shared here: https://1drv.ms/u/s!Aq2FeLHchvwogoFqQJywpf7U8Y8kKw?e=L8mfDt

Thanks,

Pragati

[AntrikshSharma]

@Pragati11  If you press F2 on any date and press enter you will see that the dates change from text to date format.

@GK600 I have made the changes for you in the excel file and Power Query also recognizes them as date. file is attached below my signaure.

[Pragati11]

HI @AntrikshSharma ,

Not sure, because at my end I see them all by default as dates. But, I don't see any APOSTROPHE character that you were talking about.

Thanks,

Pragati

[GK600]

Thanks a lot @Pragati11  and @AntrikshSharma ,

Highly appreciate your responses. Managed to learn something from both.

@Pragati, I am based in UK and prefer to stick to my default settings so prefer not to change those on my machine. However, for this exercise, I like to stick to US date format. What I am not able to understand is that why does it not convert it to US format when we specify by “using locale” and selecting English(US). It didn’t change for you either (as you mentioned your settings are as such). It should accept our selection of US format when we are specifically asking for it to the Power BI. Not sure why it’s not heeding to instructions 😊 any thoughts why?

@Antriksh, thanks for the tip of double --. Now, there are 2 points I have.

- I tried to do it at my end but get below error for any date after 12th of the month (its converting fine for upto 12th). I have a feeling my system is behaving weird with it. Any thoughts on that? The one you shared clearly seem to convert well.

- I loaded your converted file into Power BI and converts it into date format smoothly. However, its back to square 1 situation because the date format is still of UK (dd/mm/yyyy) whereas I need original US format (mm/dd/yyyy). I in fact tried to convert it by using locale etc. but it’s not changing.

@Both, wondering if you may have tip on another quick question. I am working file about 20-25 different files and combining them for my purpose. They come down to about 300-400 mb. Is there a way to shorten the size and then load into Power BI. I thought converting them into csv may help but that rather increases the size.

Thanks once again very much. Much appreciated.

Regards,

Gaurav

[AntrikshSharma]

@GK600  Sorry mate not able to figure how to get the dates to US format, I think it all comes down to the system format, becuase it works on my system with us format but not on the one with UK format, one thing you could do is to change the date format once you load the data from the Power Query to the data model.

Not sure if reducing the source file size helps in quick loading of the data, but if you can then you should load the data to a database. like SQL server.

[GK600]

Thanks @AntrikshSharma  for your response,

I don't have sql server facility as of now. I dont know how to use that either 🙂 but I think I could learn if available and useful.

I didn't know that I could simply change the date format like this in the data model so my some of the issues could be resolved with this. However, I mainly had to transform data based on this date column within Power Query. I am starting to consider different manual options though if we can't figure out. I will also raise a query with Microsoft if this is an issue as suggested by Gregg.

Thansk anyways for your help. Much appreciated. 

regards,

Gaurav

[amitchandak]

@GK600 , power bi pickup date time setting from the system. I think you have this setting

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

If yes, on option is make it US setting.

else you have create date from this

new date =
var _pos = search("/",[Date],,0)
return
date(right([date],4), left([date],_pos-1), mid([date],_pos+1,2))

[GK600]

Thansk @amitchandak  for your response. I need it to adjust it within Power Query as some further transformation needs to be done based on this Date column.

I think you suggested DAX for Power BI desktop. Can you please suggest what can be done within Power Query. Thanks. Appreciate you taking time to respond.

Gaurav 

[Greg_Deckler]

@GK600 - I am not sure I entirely follow this but when I get dates in a European format in the forums and need to convert it, I do the following:

1. In Power Query, split by /
2. Change all created columns to Text
3. Concatenate them back in the order I want in a new column, [2] & "/" & [1] & "/" & [3]
4. Convert to date
5. Remove other columns

This is all in Power Query.

[GK600]

Thanks @Greg_Deckler  for your repsonse.

I actually don't need date to convert into UK format. I want it to stick to US format (that's how it appears but with "text" as data type when loaded into Power BI). The moment, I change the data type to "Date", it starts to cause issues. 

I anyway tried as you suggested but strangely when I changed it to "Date" as Data type, it converts it into UK format (I also tried using locale). Please see below. This is quite weird as I do have US as regional setting for this file. Not sure why its pushing it to UK format. 

Any pointer will be helpful.

regards,

Gaurav

[Greg_Deckler]

@GK600 - What are your regional settings File | Options and settings | Options | GLOBAL | Regional settings and also CURRENT FILE | Regional Settings. Also check if Model language has been set.

[GK600]

@Greg_Deckler  

Thanks for your quick response.

Please see below. Model language shows "Use Application Language". 

Find below the regional settings but I tried to change it to US as well but it still converts to UK date format. (I also used "Use locale" to set the date type etc. but didnt help)

Hope this helps to review the problem.

 Gaurav

[Greg_Deckler]

@GK600- Well, my concern is that the model language was established in the UK when the report was created and if that's true, you can't reset it in Power BI (maybe the tabular editor) and that may be the problem you're having.

You can check out the Problems forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it's not there, then you could publish it.

If you have a Pro account, you can try opening a support ticket. If you have a Pro account it's free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[Syndicate_Admin]

DataSource.Error: Microsoft Informix Client: Date could not be converted to month/day/year format SQLSTATE=IX000 SQLCODE=-1210 Details: DataSourceKind=Informix DataSourcePath=10.11.0.311:9089;pbase Message=Date could not be converted to month/day/year format SQLSTATE=IX000 SQLCODE=-1210 ErrorCode=0

I changed the regional settings to Englist(United Kingdom) and (United States) but the problem persit.

In both tables de column date have the format mm/dd/yyyy.

[GK600]

Thanks a lot @Greg_Deckler ,

If that’s an actual issue, it’s surely worth raising. I will take a look and raise it if needed.

By the way, do you mind looking at my quick question above re possibility of being able to reduce size of excel sheets. That would be great help as transformation of data is taking good amount of time.

Regards,

Gaurav

[Greg_Deckler]

@GK600 - Well, so I guess the first question to ask is if these are all the same format and you are using a Folder query with combine binaries essentially and all the information essentially goes into a single table? That would be optimal since this would maximize the columnar compression of the Vertipaq engine. 

Second, turn off the auto-date hierarchy here and refresh (uncheck). Let me know results but this can reduce model size by up to 60% or so:

[GK600]

Thanks again @Greg_Deckler,

80% of the data is same format and lies in one of the folder so I think that helps. The columns may be slightly different in each file but I am OK with first file's column headings what it picks automatically (I know I can change that though). 

I was already using a Folder query. Looking at the automated steps applied by Power Query, I was not sure whether it expanded binaries column or other record/table colums. So, I manually expanded binaries column now and realised it takes same steps as earlier when expanded at source step below. Hope that makes sense and answer your question.

I wasnt sure but interstingly, Time intelligence was already switched off. See below.

Despite following this, the loading time had been somewhere upto 5-10 minutes. I am OK when I am closing and appying into Power BI desktop as thats one time but I got to simply look at all possible values in a column after a transformation step and it follows the same loading data process.

In other words, it goes through each file like below. Is there a way to know if folder query (binaries) helped reducing size? I am asking because the loading process goes through size of each file (so if a file is 40 mb in the folder, the below screenshot slowly moves up to 40mb and then move to next file.

Thanks very much again for your time to respond.

regards,

Gaurav