How do I change a 5 digit date into a dd/mm/yyyy format?

[ansa_naz]

Hi all

I have imported an Excel document into PowerBI. One of the columns in the Excel document was formatted as Date (dd/mm/yyyy) in Excel. However, in PowerBI this column displays as a 5 digit number instead of a date, with a data type of Text. If I change the data type to Date from within PowerBI, I get the following error:

DataFormat.Error: We couldn't parse the input provided as a Date value

How do I make this column show as dd/mm/yyyy, or better still, as mmm/yy?

My version of Windows is set to English UK. My version of PowerBI is set to English UK. My version of Office is set to English UK language.

I would add an image to this post but unsure how

Thanks for any and all help

Naz

[hohlick · Accepted solution]

I think it happens because you do it consequently, and PBI then decided to make this changes in one and replace first type change with second.

You can check this in steps panel - you'll see only one "Changed Type" step.

To prevent PBI from collapsing this two steps in one, you can do the next just with UI, without editing script:

1. Change type of column to Whole number.
2. Press "Fx" sign on the left to the formula bar (just above your table), then PBI makes the reference to previous step.
3. Change type to Date
4. Remove step 2 from steps list. It is not necessary, but preferrable

[ansa_naz · Accepted solution]

---

@hohlick wrote:

I think it happens because you do it consequently, and PBI then decided to make this changes in one and replace first type change with second.

You can check this in steps panel - you'll see only one "Changed Type" step.

To prevent PBI from collapsing this two steps in one, you can do the next just with UI, without editing script:

1. Change type of column to Whole number.
2. Press "Fx" sign on the left to the formula bar (just above your table), then PBI makes the reference to previous step.
3. Change type to Date
4. Remove step 2 from steps list. It is not necessary, but preferrable

---

Hi Hohlick, Im not sure what you mean by an 'fx' sign - there is no fx sign in PBI Desktop that I can see. I am in the Query Editor window, and can see no formula bar or fx sign. Please advise further. Cheers

[hohlick · Accepted solution]

Hi @ansa_naz

Please look at @Sean picture. Above first yellow frame painted on table you can see formula bar and "fx" sign

If you cannot see formula bar, in Query Editor go to "View" tab and turn this option on

[hohlick]

Here is detailed:

let
    src=#table({"a"},{{"42000"}}),
// next line generates error:
    Ch2DateErr = Table.TransformColumnTypes(Src,{{"a", type date}}),
// but this two lines generates date from text:
    Ch2Int = Table.TransformColumnTypes(Src,{{"a", Int64.Type}}),
    Ch2Date = Table.TransformColumnTypes(Ch2Int,{{"a", type date}})
in
    Ch2Date

[ansa_naz]

---

@hohlick wrote:

Here is detailed:

let
    src=#table({"a"},{{"42000"}}),
// next line generates error:
    Ch2DateErr = Table.TransformColumnTypes(Src,{{"a", type date}}),
// but this two lines generates date from text:
    Ch2Int = Table.TransformColumnTypes(Src,{{"a", Int64.Type}}),
    Ch2Date = Table.TransformColumnTypes(Ch2Int,{{"a", type date}})
in
    Ch2Date

---

Hi Hohlick, I am unsure what to do with the code you have added - do I need to enter it somewhere? Cheers

[Sean]

@ansa_naz The date data type/format is controlled by the Locale Setting in PBI

You can change the Locale setting to match the source date format OR you can split and then reassemble the date

Check out all my responses here...

http://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/m-p/40462#M15202

The last post on that thread shows all steps to convert dd/mm/yy to mm/dd/yy

Hope this helps!

[ansa_naz]

Hi Sean, Im trying to change the date from a 5 digit number to a date format. Your response doesnt really help I dont believe?? For some reason Power BI Desktop displays a date column from an Excel sheet as a 5 digit number.

---

@Sean wrote:

@ansa_naz The date data type/format is controlled by the Locale Setting in PBI

You can change the Locale setting to match the source date format OR you can split and then reassemble the date

Check out all my responses here...

http://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/m-p/40462#M15202

The last post on that thread shows all steps to convert dd/mm/yy to mm/dd/yy

Hope this helps!

---

Cheers

[v-yuezhe-msft]

Hi @ansa_naz,

As per my test, when we import data with Date type from Excel to Power BI Desktop, these data are displayed with Date type as expected.

However, based on your description, Power BI recognize your data with Date type in Excel as 5 digit number with Text type. It seems there are some issues with these Date columns in Excel, I would recommend you follow the instructions in this similar blog to firstly verify that if these data are defined with Date type or Text type.

Thanks,
Lydia Zhang

[ansa_naz]

---

@Lydia wrote:

Hi @ansa_naz,

As per my test, when we import data with Date type from Excel to Power BI Desktop, these data are displayed with Date type as expected.

However, based on your description, Power BI recognize your data with Date type in Excel as 5 digit number with Text type. It seems there are some issues with these Date columns in Excel, I would recommend you follow the instructions in this similar blog to firstly verify that if these data are defined with Date type or Text type.

Thanks,
Lydia Zhang

---

Hi Lydia, I have done what you suggested. All of the cells are of type Date. Any other ideas on what might be causing this issue? Cheers

[hohlick]

@ansa_naz, check if your source table column contains ONLY dates. Perhaps, there are also some text data, and PBI then recognises this column as text (and there are also could be errors on some rows after converting them to dates)

Hope it could help!

[ansa_naz]

---

@hohlick wrote:

@ansa_naz, check if your source table column contains ONLY dates. Perhaps, there are also some text data, and PBI then recognises this column as text (and there are also could be errors on some rows after converting them to dates)

Hope it could help!

---

Hi Hohlick, I have checked and the only cell in that column which is not a Date type is the header row. This is a Text type as it contains the column name for the data. Would that be the issue?

[hohlick · Accepted solution]

@ansa_naz, if this header row initially goes as columns header name, there are should be no problem. If it initially goes as ordinary row and you then promote this first row as a header, then.. may be. But shouldn't

[hohlick]

Hi Naz

try to convert 5-digit number to Integer type, then this integer - to date

May be it will help

[ansa_naz]

---

@hohlick wrote:

Hi Naz

try to convert 5-digit number to Integer type, then this integer - to date

May be it will help

---

Hi Hohlick, I tried to change the column data type to Whole Number, then to Date, but I got the same error

[hohlick · Accepted solution]

I think it happens because you do it consequently, and PBI then decided to make this changes in one and replace first type change with second.

You can check this in steps panel - you'll see only one "Changed Type" step.

To prevent PBI from collapsing this two steps in one, you can do the next just with UI, without editing script:

1. Change type of column to Whole number.
2. Press "Fx" sign on the left to the formula bar (just above your table), then PBI makes the reference to previous step.
3. Change type to Date
4. Remove step 2 from steps list. It is not necessary, but preferrable

[ansa_naz · Accepted solution]

---

@hohlick wrote:

I think it happens because you do it consequently, and PBI then decided to make this changes in one and replace first type change with second.

You can check this in steps panel - you'll see only one "Changed Type" step.

To prevent PBI from collapsing this two steps in one, you can do the next just with UI, without editing script:

1. Change type of column to Whole number.
2. Press "Fx" sign on the left to the formula bar (just above your table), then PBI makes the reference to previous step.
3. Change type to Date
4. Remove step 2 from steps list. It is not necessary, but preferrable

---

Hi Hohlick, Im not sure what you mean by an 'fx' sign - there is no fx sign in PBI Desktop that I can see. I am in the Query Editor window, and can see no formula bar or fx sign. Please advise further. Cheers

[hohlick · Accepted solution]

Hi @ansa_naz

Please look at @Sean picture. Above first yellow frame painted on table you can see formula bar and "fx" sign

If you cannot see formula bar, in Query Editor go to "View" tab and turn this option on

[ansa_naz]

Thanks Hohlick, thats brilliant!! Hope it helps someone else, I would never have figured that out!

---

@hohlick wrote:

Hi @ansa_naz

Please look at @Sean picture. Above first yellow frame painted on table you can see formula bar and "fx" sign

If you cannot see formula bar, in Query Editor go to "View" tab and turn this option on

---