Data modelling question: How to allocate unspecified yearly budgets based on projects' start year

[LostintheBIu]

Hi community,

I have a data modelling question, that I would really appreciate help with.

I have two source data tables. The first source data table "Yearly Budget Phasing" shows Project budgets allocated out per year, but the specific year (e.g. 2023) is not specified:

Project	Budget Year 1	Budget Year 2	Budget Year 3	Budget Year 4	Budget Year 5	Budget Year 6	Budget Year 7	Budget Year 8	Budget Year 9	Budget Year 10
1	100	150	100	100	100	100
2	100	160	60	60
3	0	60	40	90
4	50	50	50	50	50

The second data table "Project Start Year" specifies which year each Project starts in:

Project	Start Date (Year)
1	2024
2	2023
3	2022
4	2024

My desired outcome table will have the yearly budgets allocated out to specific years, based on which year the specific project starts.

For example, Project 2 starts in 2023, so Project 2's "Budget Year 1" is allocated to 2023, "Budget Year 2" is then allocated to 2024, etc.

While Project 4 starts in 2024, so Project 4's "Budget Year 1" is allocated to 2024, "Budget Year 2" is allocated to 2025, etc.

Project	Year	Budget
1	2024	100
1	2025	150
1	2026	100
1	2027	100
1	2028	100
1	2029	100
2	2023	100
2	2024	160
2	2025	60
2	2026	60
3	2022	0
3	2023	60
3	2024	40
3	2025	90
4	2024	50
4	2025	50
4	2026	50
4	2027	50
4	2028	50

Thanks for reading and thanks in advance for any assistance. 

[DataInsights · Accepted solution]

@LostintheBIu,

Try this Power Query solution. Replace the Source step with your actual source. The concept is to unpivot the Budget Year columns, merge the two tables, and create Budget Year by incrementing Project Start Year.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUTI0MACRpgYINgapgIJjdaKVjBAqzEAkglDAikF6jIEsuDITEGGJX4MJkAV2FzYCBcXGAgA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"Project " = _t, 
        #"Budget Year 1" = _t, 
        #"Budget Year 2" = _t, 
        #"Budget Year 3" = _t, 
        #"Budget Year 4" = _t, 
        #"Budget Year 5" = _t, 
        #"Budget Year 6" = _t, 
        #"Budget Year 7" = _t, 
        #"Budget Year 8" = _t, 
        #"Budget Year 9" = _t, 
        #"Budget Year 10" = _t
      ]
  ), 
  UnpivotColumns = Table.UnpivotOtherColumns(Source, {"Project "}, "Attribute", "Value"), 
  MergeProjectStartYear = Table.NestedJoin(
    UnpivotColumns, 
    {"Project "}, 
    #"Project Start Year", 
    {"Project"}, 
    "Project Start Year", 
    JoinKind.LeftOuter
  ), 
  ExpandProjectStartYear = Table.ExpandTableColumn(
    MergeProjectStartYear, 
    "Project Start Year", 
    {"Start Date (Year)"}, 
    {"Start Date (Year)"}
  ), 
  RemovePrefix = Table.ReplaceValue(
    ExpandProjectStartYear, 
    "Budget Year ", 
    "", 
    Replacer.ReplaceText, 
    {"Attribute"}
  ), 
  ChangeType = Table.TransformColumnTypes(
    RemovePrefix, 
    {{"Attribute", Int64.Type}, {"Value", Int64.Type}}
  ), 
  AddBudgetYear = Table.TransformColumnTypes(
    Table.AddColumn(ChangeType, "Budget Year", each [#"Start Date (Year)"] + [Attribute] - 1), 
    {{"Budget Year", Int64.Type}}
  ), 
  RemoveAttribute = Table.RemoveColumns(AddBudgetYear, {"Attribute"})
in
  RemoveAttribute

[LostintheBIu]

Perfect, thanks for the help!