How to repeat same set or number of values for each row in Power Query.

[Birinder]

Hi Community,

I have one query.
Does anybody know how Can I have a same set or same number of values for each row.
I will explain it better with an example.
Suppose I have a column named as "KEY" in a table named as "Date".

I want to have year from 2022 to next 4 years i.e. till 2026 for each key. Like this:

I have tried doing this, But it is not working:

let
    Source = Table.FromList(List.Distinct(#"Full BECF"[KEY])),
    StartYear = 2022,
    EndYear = 2026,
    NumberofYears = EndYear-StartYear,
    Years = List.Generate(
        () => [i=0, year = StartYear],
        each [i] < NumberofYears,
        each [i=[i]+1,year = [year]+1],
        each [year]
    ),
    Converted = Table.FromList(Years,Splitter.SplitByNothing(),{"Year"},null,ExtraValues.Error),
    Merged = Table.NestedJoin(Source,{"Column1"},Converted,{"Year"},"NewColumn",JoinKind.FullOuter),
    Expanded = Table.ExpandTableColumn(Merged,"NewColumn",{"Year"},{"NewColumn.Year"})
in
    Expanded

Can you guys help me in achieving this.
Much Much thanks in Advance.
 

[ppm1 · Accepted solution]

Start with your intial table and add a custom column with this expression

= {2022..2026}

Then hit the expand button in the column header to expand that list to new rows.

Pat

[Birinder]

@ppm1 Thanks a lot.
Crazy, how much time I was wasting instead of this simple code.