Help with creating column based on month

[SJHALANI]

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 below. Using the conditional column feature, I think I can get the first 2 values but not sure about the ones where I want the exact month basis the current month. Logic for 'One Cycle Budget' is Current month - 1 (so, eg if we are in May'23 I would want Apr'23 here). For 'Two Cycle Budget' it is Current month and for 'Three Cycle Budget' it is current month + 1 

PO Projections	Last Month of Funds
No Funds	No Funds
OK	Sufficient Funds
One Cycle Budget	Apr'23
Two Cycle Budget	May'23
Three Cycle Budget	Jun'23

Please let me know how this could be done. TIA!

[SJHALANI]

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]

Hi @SJHALANI,

Have you tried the code I supplied earlier?

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

[SJHALANI]

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

[m_dekorte]

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!

[m_dekorte]

Hi @SJHALANI,

Without an external fixed reference date, Current month logic in Power Query is always dynamic and will update with each refresh. There is no way to make that static (without a fixed reference point) this means next month, all associated values, even though they originated in a different period, would be updated to:

PO Projections	Last Month of Funds
No Funds	No Funds
OK	Sufficient Funds
One Cycle Budget	May'23
Two Cycle Budget	Jun'23
Three Cycle Budget	Jul'23

Will that meet your requirement? If not, is there a fixed reference point available in your data?

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

[SJHALANI]

Hi,

Thank you for the reply. I want it dynamic as well and so what you are saying makes sense. Next month, the values should be 'May'23', 'Jun'23', 'Jul'23'.
However, my question was more around how to code that in Power query so that I am able to create the column 'Last Month of Funds'. Would be great if I can know what code/transformation feature to use to make it work.. Thanks!

[m_dekorte]

Hi @SJHALANI 

You can give something like this a go

let
    lookIn = {{"No", "No Funds"}, {"OK", "Sufficient Funds"}, {"One", -1}, {"Two", 0}, {"Three", 1}},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stXcCvNSylW0kEwY3Wilfy9gSLBpWlpmcmZqXklyDJ5qQrOlck5qQpOpSnpqSVAdY4FRTGlBgZG5kbGYCUh5fnoSnwTK1GVZBSlYpjjVZqHUBQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO Projections" = _t, #"Last Month of Funds" = _t]),
    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

It's a bit more elaborate but scalable and avoids duplication of code.

All the way at the top I've added a lookIn list with nested lists that each contain 2 items, a part to lookFor and a part to return. When the return value is not a numer the conversion to date will raise an error and try~otherwise will return the return value instead of the error.

As you can see here

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