Forum Discussion

Einomi's avatar
Einomi
Icon for Helper V rankHelper 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...
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

Einomi's avatar
Einomi
Icon for Helper V rankHelper 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