Can you set values based on the column name and the current year

[Anonymous]

I have a few columns with budgets for each year. The column names contain the years. 

I want to make a column that copies the values from the column name that contains the current year.                       

Like this:                                                              (2023) 
                                                                               V

Budget2022	Budget2023	Budget2024	BudgetCurrentYear
1,000,000	500,000	700,000	500,000
2,000,000	400,000	300,,000	400,000
3,000,000	600,000	900,000	600,000

[DataInsights · Accepted solution]

@Anonymous,

This Power Query solution uses Unpivot Columns. I added an Account column--you'll likely have numerous dimension columns such as Company, Account, and Department.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjQwUNJRMtQxMDAAYSDbFM4yh7JidaKVjMAiRkjqTOAsYyALrtAYIoSk0AzOsoQZGAsA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Account = _t, Budget2022 = _t, Budget2023 = _t, Budget2024 = _t]
  ), 
  ChangeType = Table.TransformColumnTypes(
    Source, 
    {{"Budget2022", Int64.Type}, {"Budget2023", Int64.Type}, {"Budget2024", Int64.Type}}
  ), 
  UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"Account"}, "Attribute", "Value"), 
  SplitColumn = Table.SplitColumn(
    UnpivotColumns, 
    "Attribute", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Attribute.1", "Attribute.2"}
  ), 
  RemoveColumn = Table.RemoveColumns(SplitColumn, {"Attribute.1"}), 
  RenameColumn = Table.RenameColumns(RemoveColumn, {{"Attribute.2", "Year"}}), 
  ChangeType2 = Table.TransformColumnTypes(RenameColumn, {{"Year", Int64.Type}}), 
  AddIsCurrentYear = Table.AddColumn(
    ChangeType2, 
    "Is Current Year", 
    each if [Year] = Date.Year(DateTime.LocalNow()) then 1 else 0
  ), 
  ChangeType3 = Table.TransformColumnTypes(AddIsCurrentYear, {{"Is Current Year", Int64.Type}})
in
  ChangeType3

In your measures, you can add a condition for Is Current Year = 1.

[Anonymous]

Thank you this is amazing. I spend way to long trying to solve this myself.