Transpose Table that has duplicate rows in 1 column, but unique in others

The operation you need is a Pivot Table with no aggregation.

However, if you merely select that in the UI, you will get errors returned because of the multiple entries for each column.

To avoid that, you can either group by each entry type (eg each 8 rows in the current data) and then pivot each subgroup individually, or you can use a custom function developed by Cam Wallace.

I chose the latter since it seems to run faster.

Custom Function

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

//Rename:  fnPivotAll 

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

Main Code

let

//change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table23"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"label", type text}, {"response", type any}}),

//Pivot the label column with no aggregation using a custom function
    pivot = fnPivotAll(#"Changed Type","label","response"),

//set the data types
    #"Changed Type1" = Table.TransformColumnTypes(pivot,{
        {"Title Page", type text}, {"Site conducted", type text}, {"Conducted on", type datetime}, 
        {"Prepared by", type text}, {"Location", type text}, {"Date for Data", type datetime}, 
        {"Shift", type text}, {"How Many Units made", Int64.Type}})
in
    #"Changed Type1"

Results from your "Current"