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
m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

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 ProjectionsLast Month of Funds
No FundsNo Funds
OKSufficient Funds
One Cycle BudgetMay'23
Two Cycle BudgetJun'23
Three Cycle BudgetJul'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's avatar
    SJHALANI
    Helper I
    3 years ago

    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's avatar
      m_dekorte
      Resident Rockstar
      3 years ago

      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!