Automatically generate columns in Power Query

Hi snowrider1799 ,

This is a solution, but I agree with Greg_Deckler , you may want to revisit the model, you may be better off using the migration to PBI as an opportunity to improve the model.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvJDcBACEPRXjiP5LBFSS2I/tsIMJnb15MdQQIGO60dL+UKcvgxOTb5lGmFXGPWu7aO324omMsm9ldhYJuv9SHzAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"File Date" = _t, #"Expiry Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Date", type date}, {"Expiry Date", type date}}),

    //Plug in your source table here
    SourceTable = #"Changed Type",
    
    //This builds a list of years from the data
    ListOfYearsUsed = List.Sort(List.Transform(List.Combine({SourceTable[File Date], SourceTable[Expiry Date]}), Date.Year)),
    //Please feel free to replace with manual year range which looks like {2010..2020}
    ListOfYears = {ListOfYearsUsed{0}..List.Last(ListOfYearsUsed)},

    //This cycles through the list of years and add columns for each year
    #"Added Custom" = List.Accumulate(ListOfYears, SourceTable, (a, n)=> Table.AddColumn(a, Text.From(n), each if Date.Year([File Date]) <= Number.From(n) and Date.Year([Expiry Date]) > Number.From(n) then 1 else null, type number))
in
    #"Added Custom"

Kind regards,

JB