dataformat.error we couldn't parse the input provided as a date value

Question

good morning,
while changing the type of one column from Text to Date, power query result in DataFormat.Error only in one specific cell with "mar-2020".
If I change "mar-2020" to "marzo-2020" I have no error.
Here the link of the xslx
Here the link of the sequence error

Answer 1

Hi @mlk

I can repro (with Excel 365 v2011 build 13426.20404 / PQ v2.87.846.0) the issue with another query + Power Query regional settings = Italian (Italy)

As a workaround you can setup a separate query with the following code:

let  
    Source = Table.FromColumns(  
        {  
            {"gen","feb","mar","apr","mag","giu","lug","ago","set","ott","nov","dic"},  
            {"gennaio","febbraio","marzo","aprile","maggio","giugno","luglio","agosto","settembre","ottobre","novembre","dicembre"}  
        },  
        type table [ShortMonth=text,MonthName=text]  
    )  
in  
    Source  

and merge it with your main query to "replace" the short month names. See how here

Answer 2

ciao Lz-3068
thank you for your workaround!
But it is not what I'm looking for :D
My personal workaround, as I wrote, is to manual rename short month name to full month name in exccel before edit in PQ.
may be it's a bug?
:O
happy new year