I have a column with values that repeat and need the unique values to be column headers. Then, I need to add the associated data as a row. I have 20k of these rows and cannot figure out how to transpose them. Any help is greatly, greatly appreciated.

How my data looks now:

How I need it to look:

I have a column with values that repeat and need the unique values to be column headers. Then, I need to add the associated data as a row. I have 20k of these rows and cannot figure out how to transpose them. Any help is greatly, greatly appreciated.

How my data looks now:

How I need it to look:

• excel
• excel-formula
• powerquery
• transform
• transpose

• 1 Does each block always contain all 4 entries? And, are they always in the same order? (i.e. if "Location" is optional and may be missing, then that will change the possible solutions) – Chronocidal Commented Feb 17 at 11:33

4 Answers 4

You can apply this formula

=LET(tab,H14:I25,
frow,TRANSPOSE(UNIQUE(CHOOSECOLS(tab,1))),
dat,WRAPROWS(CHOOSECOLS(tab,2),COLUMNS(frow)),
VSTACK(frow,dat))

where H14:I25 is the range of the data to transform.

Haven't tested efficiency, but these are two options if the values in column A don't repeat in same order (M365 required):

=LET(range,A:.B,DROP(PIVOTBY(MAP(INDEX(range,,1),LAMBDA(x,SUM(N(TAKE(range,1,1):x=x)))),INDEX(range,,1),INDEX(range,,2),SINGLE,0,0,,0),,1))

=LET(range,A:.B,a,TAKE(range,,1),u,UNIQUE(a),DROP(REDUCE(0,u,LAMBDA(x,y,IFNA(HSTACK(x,VSTACK(y,FILTER(DROP(range,,1),a=y))),""))),,1))

In Power Query you can

Add this custom function:
Be sure to rename it as in the comments

//credit: Cam Wallace  https://www.dingbatdata/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotNoAggregation

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Then use this for the main code (changeing the Source line to reflect your actual table name):

let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    Pivot = fnPivotNoAggregation(#"Changed Type","Column1","Column2")
in
    Pivot

In powerquery, perhaps not as fast, but easier, assuming its always 4 rows that repeat in the same order

Add column ... index column....
Click on that new index column and transform ... standard ... integer-divide ... 4
Click select first column
Transform ... pivot column ... values column: column2, Advanced: do not aggregate

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column1]), "Column1", "Column2")
in #"Pivoted Column"