Power Query Custom Column:-

Extract GR No.
if Value.FromText(Text.Middle([Parent],4,2)) = 11 then Text.End([Name],5) else if Value.FromText(Text.Middle([Parent],4,2)) = 12 then Text.End([Name],5) else Value.FromText(Text.End([Name],4))

Status
if [STD] = 11 then "New" else if [STD] = 12 then if [Q1 Balance] = 0 then "CD" else "CND" else if [#"GR No."] > 4354 then "NEW" else if [Q1 Balance] = 0 then "CD" else "CND"

Today
DateTime.LocalNow()

Extract No, of Days
if Duration.Days(Date.From(DateTime.LocalNow()) - Date.From("02-05-2024")) < 0 then 0 else if [Q1 Balance] > 0 then Duration.Days(Date.From(DateTime.LocalNow()) - Date.From("02-05-2024"))

H
#

H
#

H
#

H
#

H
#

H
#

H
#

H
#

H
#

---

Power Query Function:-

          
1. Convert Roman to Number:-
          let
          RomanToNumber = (romanText as text) as number =>
          let
              RomanMap = [
                  M = 1000, CM = 900, D = 500, CD = 400,
                  C = 100, XC = 90, L = 50, XL = 40,
                  X = 10, IX = 9, V = 5, IV = 4, I = 1
              ],
              ToList = List.Buffer(Text.ToList(Text.Upper(romanText))),
              
              GetValue = (pair as list) as number =>
                  if List.Count(pair) = 2 and Record.HasFields(RomanMap, Text.Combine(pair))
                  then Record.Field(RomanMap, Text.Combine(pair))
                  else Record.Field(RomanMap, pair{0}),
              
              Process = (lst as list, acc as number) as number =>
                  if List.IsEmpty(lst) then acc
                  else if List.Count(lst) >= 2 and Record.HasFields(RomanMap, Text.Combine({lst{0}, lst{1}}))
                  then @Process(List.Skip(lst, 2), acc + GetValue({lst{0}, lst{1}}))
                  else @Process(List.Skip(lst, 1), acc + GetValue({lst{0}})),
              
              Result = Process(ToList, 0)
              in
              Result
              in
          RomanToNumber
        

---

          
2.Get Financial Year :-
          let
          GetFinancialYear = (inputDate as date) as text =>
          let
              year = Date.Year(inputDate),
              month = Date.Month(inputDate),
              fyStart = if month >= 4 then year else year - 1,
              fyEnd = if month >= 4 then year + 1 else year,
              result = Text.From(fyStart) & "-" & Text.End(Text.From(fyEnd), 2)
              in
                  result
          in
              GetFinancialYear          
        

---

          
3.**Reusable Function** :-
            ExtractMonthYear = (inputDate as date) as text =>
                Date.ToText(inputDate, "MMM-yy")

            .................Add Column
            = Table.AddColumn(YourTableName, "Month-Year", each Date.ToText([Date], "MMM-yy"), type text)
            .................
        

---

          
2. :-

          

---

          
2. :-

          

---

          
2. :-
          
        

---