Repeat a small list of values on the whole column (without creating duplicate rows)

[Stella_V]

Hello 

I need to repeat a set of text values in the whole column, let's say: 

Apple

Oranges

Pears

Apple

Oranges

Pears

Apple

Oranges

Pears

Apple

Oranges

Pears

Apple

Oranges

Pears

I tried with the custom column : = Table.AddColumn(#"Added Conditional Column", "Custom", each {"Apple", "Orange", "Pears"}) but it gives me a list which then when I extend, is creating 3 new rows for one old. 

Any idea how to solve that?

thanks a lot

Kind regards

Stella

[Stella_V]

thank you 🙂

[v-cgao-msft · Accepted solution]

Hi @Stella_V ,

Consider adding an [Index] column before creating a custom column like this:

if Number.Mod([Index],3) = 1 then "Apple" else if Number.Mod([Index],3) = 2 then "Oranges" else "Pears"

Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcW5DQAgDACxXVKzBF8kfqij7L8G0rmxmcQoHkxSopypFKqVVKk16p3GoDlpLdqbzqF76T1x/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Number.Mod([Index],3) = 1 then "Apple" else if Number.Mod([Index],3) = 2 then "Oranges" else "Pears")
in
    #"Added Custom"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HotChilli]

List.Repeat