Month column not properly formatted

[Anonymous]

Hi,

I have monthly data imported for 10 months from 10 excel sheets from which the Month was extracted from the file names using "Text between Delimiters", the data type of the Month column is text. The column is Month and the values are - 

Apr20, Mar20, Feb20, Jan20, Dec19, Nov19, Oct19, Sep19, Aug19, Jul19.

When I change the format to Date, the values from Dec19 to Jul19 become Dec20,.....,Jul20. 

Please help with this.

Thanks,

Vishy

[V-lianl-msft]

Hi @Anonymous ,

I tried to reproduce your issue, but it failed.It works as expected.

Can you share some screenshots and M query code?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft ,

PFA the sample pbix along with the sample data files with the file names similar to how it actually is.

https://drive.google.com/file/d/1jyfzuN5JNOhju2qAkZNiH17uGwvSRrEg/view

I am facing the same issue above where the Dec19 (i.e. Dec 2019) file name gets converted to Dec20 when I change the data type to Date.

Let me know if you have any questions.

Thanks,
Vishy

[amitchandak]

@Anonymous , Try like

=Text.start([date],3) & (Text.End([date],2)*1+1)

[Anonymous]

Hi @amitchandak ,

That didn't work. Let me try giving you some more details on this column. As I mentioned, I am importing data from 10 different excel sheets. Sample file name of these excel sheets are "XYZ_Apr20", "XYZ_Mar20" and so on.

I created a Month column using "Text between Delimiters" as mentioned before to just get the Month and Year so my Month column has values Apr20, Mar20 and so on. The data type of this column is Text. When I change the data type to Date, Jul19 to Dec19 becomes Jul20 to Dec20 which is incorrect.

Hope this gives the required info, let me know if you have additional questions.

Regards,

Vishy 

[amitchandak]

@Anonymous , Make them a date column

Date = "01-" & left([Month],3) & "-" & right([Month],2)

Mark that as date , then try

Date new= date(year([Date])+1,month([Date]),Day([Date]))

[Anonymous]

Hi @amitchandak ,

I believe the solution suggested by you is with DAX. Is there any way to do it in the Query Editor itself before loading the data?

Regards,

Vishy