Forum Discussion
Solved
Create a column with a repeating sequence
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...
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,
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
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)"
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)"