Forum Discussion

Einomi's avatar
Einomi
Helper V
3 years ago
Solved

From YYYYMM to mmm YY

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

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Einomi's avatar
    Einomi
    3 years ago

    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

2 Replies

  • Einomi's avatar
    Einomi
    Helper V
    3 years ago

    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