0

I have a table in Excel 2016:

enter image description here

      YEAR     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

Problem:

I need at least one row for each year within this year range: system year + 9. In other words, I want rows for 10 years, starting with the current year (currently 2023).

As you can see, there are rows missing for these years: 2023, 2030, and 2032. So I want to use Power Query to generate rows for the missing years.

It would look like this:

enter image description here

Question:

Using Power Query, how can I generate filler rows for the missing years?

  • I'm looking for a dynamic solution; I don't want to manually enter filler rows into the table.
  • On January 1st 2024, I want the the year range to automatically switch from 2023-2032 to 2024-2033. I don't want to hardcode the year range.
User1974
  • 185

1 Answers1

1

Assuming your table is named Table1, first create a new query - named Years - which generates a dynamic list of the missing years you require:

let
  Source = List.Numbers(Date.Year(DateTime.LocalNow()), 10), 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", Int64.Type}}), 
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "YEAR"}}), 
  #"Merged Queries" = Table.NestedJoin(
    #"Renamed Columns", 
    {"YEAR"}, 
    Table1, 
    {"YEAR"}, 
    "Table1", 
    JoinKind.LeftAnti
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Merged Queries", {"Table1"})
in
  #"Removed Columns"

It's important that the table header in the above is renamed to match the YEAR column in your original table; hence the choice of "YEAR" (upper-case) in the #"Renamed Columns" step.

You can then create a final query:

let
  Source         = Table.Combine({Table1, Years}), 
  #"Sorted Rows" = Table.Sort(Source, {{"YEAR", Order.Ascending}})
in
  #"Sorted Rows"
Jos Woolley
  • 541