Forum Discussion

SJHALANI's avatar
SJHALANI
Helper I
3 years ago

Help with creating column based on month

Hi All, I want to create a new column ('Last Month of Funds') in my table which has values based on another column ('PO Projections'). The corresponding values that I want are listed in the table...
Need Help
SJHALANI's avatar
SJHALANI
Helper I
3 years ago

Hi,

Using the "Conditional Column" feature, I have used the below code:

= Table.AddColumn(#"Removed Duplicates1", "Last Month of Funds", each if [PO Projections] = "No Funds" then "No Funds" else if [PO Projections] = "One cycle budget" then "Apr'23" else if [PO Projections] = "Two cycles budget" then "May'23" else if [PO Projections] = "Three cycles budget" then "Jun'23" else if [PO Projections] = "OK" then "Sufficient for next 3 service months" else [PO Projections]).

What I want to do the above code is, insted of hardcoding "One cycle budget" to "Apr'23", I want it to be dynamic so that it codes it as (Current month - 1). Similarly "Two cycles budget" should be (Current Month) and "Three cycles budget" should be (Current Month + 1). So the question simply is how do I make the hardcoded values Apr'23, May'23 and Jun'23 based on the current month?

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

Hi SJHALANI,

 

Have you tried the code I supplied earlier?

You can copy the full script into a new blank query.

  • SJHALANI's avatar
    SJHALANI
    Helper I
    3 years ago

    Hi,
    I am getting the below error: (see screenshot)

    • m_dekorte's avatar
      m_dekorte
      Resident Rockstar
      3 years ago

      Hi SJHALANI,

       

      You haven't copied the code into a new blank query but in a step...

      Create a new blank query, open the advanced editor window, select all you see there and replace it with the supplied code.

       

      If you want to implement this code into your own query, here are the steps.

      Select your query, open the advanced editor window, place your cursor after the in-clause, enter to go to a new line and paste in this code:

      lookIn = {{"No", "No Funds"}, {"OK", "Sufficient Funds"}, {"One", -1}, {"Two", 0}, {"Three", 1}},

       

      Now select the in-clause and all that follows and copy this in its place:

          AddColumn = Table.AddColumn( Source, "NewColumn", each 
        let v = List.Select( lookIn, (x)=> x{0} = Text.BeforeDelimiter([PO Projections], " " )){0}{1} in 
        try Date.ToText( Date.AddMonths( Date.From( DateTime.FixedLocalNow()), v), [Format = "MMM yy", Culture="en-US"] ) 
        otherwise v, type text
    )
in
    AddColumn

       

      Where it says "Source" on this line: AddColumn = Table.AddColumn( Source

      Replace that with the previous step name, just copy it from before the equals sign

       

      Double check that your table includes a column with this name: [PO Projections]

      and that should be it.

       

      Ps. If this helps solve your query please mark this post as Solution, thanks!