Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

How to create a new formatted YYYY-YY column based on the row's date month value?

Hi, In a single table I have a date column named 'Filing Date'. I would like to create a new column, in the same table, using the following logic. If the Filing Date's month is 6 or greater (so 6 t...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi D_PBI ,
    Lot of work! I wonder if you meant 7 or greater thus splitting the year in half. If so, change the code at the beginning replacing the 6 with 7.

    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    if Date.Month ([Column1])<6 then Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))-1),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2)))},"-" ) else Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2))+1)},"-" )

     

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi D_PBI ,
Lot of work! I wonder if you meant 7 or greater thus splitting the year in half. If so, change the code at the beginning replacing the 6 with 7.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

if Date.Month ([Column1])<6 then Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))-1),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2)))},"-" ) else Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2))+1)},"-" )

 

 

  • D_PBI's avatar
    D_PBI
    Icon for Post Partisan rankPost Partisan
    6 years ago

    Thanks Nathaniel_C  - this is much appreciated.

    For others who may view this solution, for me, the only addition I needed to apply is to just add 'each' before the 'if' at the beginning of the M code.
    Thanks again for your speedy help and Kudos for sure 👍

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago

      D_PBI ,

      You are welcome! It woulde have been better if I mentioned that I built this in a custom column, and the each is not required.  Power Query puts it in for you, althought it is good practice to put it in even so.
      Cheers,

      Nathaniel

       

      • D_PBI's avatar
        D_PBI
        Icon for Post Partisan rankPost Partisan
        6 years ago

        Nathaniel_C  - I knew you hadn't left of the 'each' by mistake. I knew in your example you didn't need it. I added the 'each' comment in case others applied the code directly in Advanced Editor and couldn't understand why it raised an error.

         

        Contributions like yours are very important to the community so keep up the good work.

  • Magnum_BI's avatar
    Magnum_BI
    Frequent Visitor
    3 years ago

     

    Hi,

     

    Think that when using 'Number' component, you'll hit problems with leading zeroes.  I.e. 2008/09 will appear as 2008/9.

     

    Alternate method (Australian Financial Year Jul-Jun) -

     

     
    if Date.Month([Date]) >= 7 then
    Text.From(Date.Year([Date])) & "/" & Text.End(Text.From(Date.Year([Date])+1), 2)
    else
    Text.From(Date.Year([Date])-1) & "/" & Text.End(Text.From(Date.Year([Date])), 2)
     
    Will result in 2008/09