Forum Discussion

Ori_I13's avatar
Ori_I13
New Member
3 years ago
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 distinct value from the first column:

100072023
100072024
100072025
72492023
72492024
72492025

etc...

 

How can i make this kind of table?

Thanks

Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    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,

5 Replies

  • Tom_L's avatar
    Tom_L
    New Member
    1 year ago

    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.

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    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,

  • paladin21's avatar
    paladin21
    Regular Visitor
    3 years ago

    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)"