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

[iamtommoore]

Hello,

i am trying to transpose this table... so that the Column "Label" is the top row header, and anything in response is then transposed below... 

however when i do this it just duplicates each row into a new column

hopefully the below current and desired will give you an idea

to this

Title Page	Site conducted	Conducted on	Prepared by	Location	Date for Data	Shift	How Many Units made
A	B	2022-07-22T21:59:38.303Z	Tom Moore	C	2022-07-20T23:00:00Z	Morning	5422
A	B	2022-07-22T22:43:01Z	Tom Moore	C	2022-07-14T22:43:20Z	Nights	200

[iamtommoore]

Current

label	response
Title Page	A
Site conducted	B
Conducted on	2022-07-22T21:59:38.303Z
Prepared by	Tom Moore
Location	C
Date for Data	2022-07-20T23:00:00Z
Shift	Morning
How Many Units made	5422
Title Page	A
Site conducted	B
Conducted on	2022-07-22T22:43:01Z
Prepared by	Tom Moore
Location	C
Date for Data	2022-07-14T22:43:20Z
Shift	Nights
How Many Units made	200

[ronrsnfld · Accepted solution]

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"

[Moonlight987]

Hello ronrsnfld, thanks for the advice, it is really useful. But what if responses are in several columns instead of one (I mean - there is label column and then response column, response1 column, response2 column, etc.)? How this should be done? Thanks in advance for any kind of advice. 

[ronrsnfld]

@Moonlight987 I suggest you post a new question with a textual example of representative of your data, and at least a screenshot of what you expect for results. It is all doable, but the specifics depend on your actual data set.

[Moonlight987]

Thanks @ronrsnfld for prompt reply, new chain created with Title: "Transpose Table - duplicate rows in 1 column (label column), several columns with unique values".

[ronrsnfld]

Which is "current"?  If "current" is the screenshot, please post it as text which can be easily copy/pasted.

[iamtommoore]

Hello, Sorry im new to this! 

i have added the current. any ideas?