pivot/transpose/fill

[KCantor]

I am quite positive this will be a simple task for some here. I need to take a list of substitutions and make a table of substitutions by filling in rows with the exact same information. So, if I start with a table like this:

Pet

Dog

Cat

Fish

Mouse

Turtle

Snake I can get this: Pet Dog Cat Fish Mouse Turtle Snake Cat Fish Mouse Turtle Snake Dog Fish Mouse Turtle Snake Dog Cat Mouse Turtle Snake Dog Cat Fish Turtle Snake Dog Cat Fish Mouse Snake Dog Cat Fish Mouse Turtle

Is there any help for me? I can pull in, clean, and make data useful but I must be making this too difficult.

 

[Bill Szysz]

Hi KCantor,
Try this code below

let
    Source = Excel.CurrentWorkbook(){[Name="PetTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pet", type text}}),
    HowManyRows = Table.RowCount(#"Changed Type"),
    Double = #"Changed Type"[Pet] &  #"Changed Type"[Pet],
    ListOfColumns = List.Transform(List.Positions(#"Changed Type"[Pet]), each List.Range(Double, _, HowManyRows)),
    Result = Table.FromColumns(ListOfColumns)
in
    Result

Regards