Power Query importing excel date field mmm-yy(Dec-21) automatically to 12/21/2022

[SaulM]

Hello everyone, 

I am banging my head trying to fix something what seems simple. I have some excel files with a column "Date" formated as "DEC-21"(text) and Power Query brings it in automatically as 12/21/2022, making the year "21" to a date and adding the year of 2022 (guessing the year it's imported). How can I fix this? I can't split by delimiter and then merg as it's automatically changing it to 12/21/2022.

Excel: DEC-21          Power Query: 12/21/2022

I appreciate any help.

Thanks,

Saul

[ronrsnfld · Accepted solution]

If by "fix this" you mean you want to display it the same as in your original Excel sheet, then:

Your data in Excel is a Text string but Power Query is changing it to a Date.  All you need to do is ensure PQ treats it as text.

If you examine the Applied Steps window, you will see that (probably) the second step is something like "Changed Type".  If you select that step, you will see in the formula bar something like: 

=Table.TransformColumnTypes(Source,{{"Date", type date}})

There may be other column names and types also in that line.

All you need to do is edit that line to change type date to type text

If you mean something else by "fix this" then please explain precisely what you want.

[SaulM]

That's it, ronrsnfld!

I made that change in the import table, but I did not see it in the function. I found it in the function and made that change.

Now, that it's in text and Mmm-yy format, I was able to split and merge again to make it Mmm-yy and Date format.

 

Thanks for your help! Simple, but it got me.

Saul

[v-yalanwu-msft]

Hi, @SaulM ;

It is very strange that you can share your excel file to remove sensitive information? So that I can test it

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SaulM]

Looks like my work network prevents me to share or connect to any fileshare sites, Yalan. I was able to recreate the sheet from scratch and it did the same thing within Power Query. Below are random number. If you type DEC-21 (no spaces) it will recreate the issue.

Thanks!

Saul

[v-yalanwu-msft]

Hi, @SaulM ;

You could delete last step in power query.

Then add custom column.

=Date.FromText(
    "20"& Text.AfterDelimiter([Date], "-")&"-"&
     Text.BeforeDelimiter([Date], "-")
     &"-1")

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcklN1jUyVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText(
    "20"& Text.AfterDelimiter([Date], "-")&"-"&
     Text.BeforeDelimiter([Date], "-")
     &"-1"))
in
    #"Added Custom"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SaulM]

Thanks for help Yalan. That solution is does not work in this scenario. 

As soon as it's imported in, it Power Query automatically set's it to mm/dd/yyyy format in the 1st step.

I am bringin it in from a function to combine all files to one table.

 

That's my problem. So I can't referance the date in Mmm-yy format in power query as it automatically thinks it's in mm/dd/yyyy format and giving the year the import year.

Thanks,

Saul

[Ehren]

Some questions:

• How are these date values stored in Excel? Are they text or actual dates?
• How are you importing them? Is the data in the current workbook, or an external workbook?
• If the data is coming from an external workbook, what format is the workbook saved in (e.g. .xlsx or .xls)?