in excel Month is Date type, but when imported into power query it cannot be changed to Date?

[Anonymous]

hi experts!

I have an excel file and there is a month row in the type of Date:

however, when i imported it into power query, the months are recognized as Text data type and cannnot be converted into date type, what will show error.

i am wondering why it is not feasible to do that in power query???

and i have one way to solve it, it is that in excel i change 3-letters months into d-m-yyyy and then change this into custom type "mmm".

May I ask if there is any other better ways that can achieve the same results by just processing data in query, not changing the data in excel. becasue it is better to keep the data source unchanged.

Thanks in advance!

[ronrsnfld]

In Excel, if you select a cell showing the Month Name, what exactly do you see in the Formula Bar?

[Anonymous]

Hi, thank you for you reply.

you can see in the first picture, i selected cell Aug, and the formula bar also shows Aug, but, the format show on the top is Date.

[ronrsnfld]

In Excel, changing the format does not change the data type.

In your case, the data type is still text.

Excel stores dates (and times) as numbers equivalent to days (and fractions of a day) since 1 jan 1900.

Since your data is TEXT and not a real date, Power Query has no way of knowing what the actual date might be.

If the languages are compatible, Power query can convert a string like "Aug-1" as suggested by @wdx223_Daniel into a date, but it will assume the year is the current year.

If you want it to NOT make that assumption, you will need to create a rule by which PQ can decide on a year.

Changing it in Excel, and formatting as "mmm" is also a possibility so long as the months are not being used as column headers (which will convert them into text when you create the table)

[Anonymous]

Hi, 

thank you for explaning the "why"!

and i think the alternative way u mentioned is what i am doing now. i have to change the month cells in excel. 

Considering that in my excel data source, there are no other date/year cells than month cells in TEXT, it is hard for me to create some rules in power query to make it decide on a year, i will just take the alternative way.

thank you!

[wdx223_Daniel]

=Table.TransformColumns(PreviousStepName,{"MonthDate",each Date.FromText(_&"-1")})

[Anonymous]

Hi Daniel,

I realized another problem with the code u provided.

in my excel data, i have month from Aug (2023) till Feb (2024) (but the years are not shown in the cells). but with ur code, the Jan and Feb will be transfromed into 1-1-2023 and 1-2-2023, while actually they shall be 1-1-2024 and 1-2-2024.

may i ask is there a way to solve this problem?

thanks in advanace!

[Anonymous]

Hi Daniel,

Thanks! it works well!

But i am not very celar about the code. if you have time, could you please explain Date.FromText(_&"-1")? i dont know what  _&"-1" means.