Forum Discussion

SaulM's avatar
SaulM
Frequent Visitor
3 years ago
Solved

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

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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    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.

     

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
3 years ago

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's avatar
    SaulM
    Frequent Visitor
    3 years ago

    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