Create a column with a repeating sequence

[Ori_I13]

Hi everyone, 

Currently, I have a column that contains distinct values, as such: 

10007

7249

59

7882

11005

I would like to add another column of a repeating sequence, for each distinct value from the first column:

10007	2023
10007	2024
10007	2025
7249	2023
7249	2024
7249	2025

etc...

How can i make this kind of table?

Thanks

[Tom_L]

I know it's been asked a while ago, but I came across this today, and there will be people asking this question again.

The quickest way if there aren't many values (like in your example only 2023, 2024 and 2025) is to add a list of these values as a new column and expanding it. 

Otherwise as paladin21 suggested below, merge it from a separate table. That gives you flexibility to change the numebrs in source file instead of the query.

[Ruben-C1]

You are a genius thank you very much

[paladin21]

I would use the Merge Queries option in Power Query (https://learn.microsoft.com/en-us/power-query/merge-queries-overview). To do so, I'd create an auxiliar column with a constant in the main table and a second table with said constant in the first column and the sequence in the second column. Then merge the tables using the constant as key and expand the sequence and delete the auxiliary column. This is a graphic sequence of actions to achieve it and the code for the main table.

Spoiler

Spoiler

let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBXitWJVjI3MrEEM0whlLmFhRGYYQhUY6oUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Columna1 = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Columna1", Int64.Type}}),
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "Personalizado", each "A"),
#"Consultas combinadas" = Table.NestedJoin(#"Personalizada agregada", {"Personalizado"}, #"Tabla (2)", {"Columna1"}, "Tabla (2)", JoinKind.LeftOuter),
#"Se expandió Tabla (2)" = Table.ExpandTableColumn(#"Consultas combinadas", "Tabla (2)", {"Columna2"}, {"Tabla (2).Columna2"})
in
#"Se expandió Tabla (2)"

[rsbin · Accepted solution]

@Ori_I13 ,

The CROSSJOIN function will work for you:

ValuesNew = CROSSJOIN( 'Values',
                     UNION( 
                         ROW("Sequence", 2023),
                         ROW("Sequence", 2024),
                         ROW("Sequence", 2025)))

'Values' is the name of your first table.

You can replace the UNION part with the name of a second table if you have many more values than the 3 you have shown in your example.

Regards,

[Ori_I13]

Thank you very much!

Worked like a charm