From YYYYMM to mmm YY

[Einomi]

Hi,

I have loaded my data to PQ and I have a date (DD/MM/YYYY) column. To be able to sort the date from the oldest to the newest, I have added a custom column

Date.Year([Date])*100 + Date.Month([Date])

this gives me a new column with the following format date YYYYMM

So far so good.

The issue is that when I load my data back to Excel I do not want this format, rather I would like this format MMM YY so that it is clearer. I have attached a pic to illustrate.

Any help will be appreciated

 

[Einomi]

Anyone has an idea ?

[Einomi · Accepted solution]

I found a way to do it, here is the code

YYYYMM  = Table.AddColumn(#"Colonnes permutées", "YYYY_MM", each Date.Year([Date])*100 + Date.Month([Date])),
    YY = Table.AddColumn(YYYYMM, "Year", each Text.Middle(Text.From([YYYY_MM], "en-GB"), 2, 2), type text),
    mmm_YY = Table.AddColumn(YY, "MMM", each  Date.MonthName( Number.FromText( (Text.End(Text.From([YYYY_MM], "en-GB"), 2)))*29)),
    Deleted = Table.RemoveColumns(mmm_YY,{"Posted", "Name", "No.", "Amount"}),
    Format_MMM = Table.TransformColumns(Deleted, {{"MMM", each Text.Start(_, 3), type text}}),
    Final = Table.CombineColumns(Format_MMM,{"MMM", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Month")
in
    Final

but maybe someone has a better code to do it in less steps 🙂

Thanks for your time