pivot/transpose/fill

KCantor · Oct 28, 2016

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 · Oct 29, 2016

Hi KCantor,
Try this code below

Code:

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

pivot/transpose/fill

KCantor

New member

Bill Szysz

Member