Forum Discussion

LostintheBIu's avatar
LostintheBIu
Icon for Helper II rankHelper II
2 years ago
Solved

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

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 1Budget Year 2Budget Year 3Budget Year 4Budget Year 5Budget Year 6Budget Year 7Budget Year 8Budget Year 9Budget Year 10
1100150100100100100    
21001606060      
30604090      
45050505050     

 

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

ProjectStart Date (Year)
12024
22023
32022
42024

 

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.

 

ProjectYearBudget
12024100
12025150
12026100
12027100
12028100
12029100
22023100
22024160
2202560
2202660
320220
3202360
3202440
3202590
4202450
4202550
4202650
4202750
4202850

 

Thanks for reading and thanks in advance for any assistance. 

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    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

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    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