How to use non first row as column header

[awsiya]

Hi 
Below is a table where I want to utilise the column labeled "toprow," specifically when its value is 0, as the column header. I attempted to transpose the table, but it didn't yield the desired outcome, likely due to an incorrect procedure. The resulting column headers should represent aspects such as hygiene of food, overall feedback, etc. How can I achieve this?

Toprow	QID1	QID2	QID3	QID4	QID5
1	Yes	Good	18/05/2022	Yes	Yes
1	some extend	Bad	25/06/2023	Yes	Yes
1	No	Bad	13/01/2024	some extent	Yes
1	Yes	Excellent	05/02/2024	No	No
0	Was food Hygienic	Overall Feedback	27/02/2024	Reception treated well	AirCon cool enough?
1	Yes	Good	18/06/2022	No	No
1	some extend	Bad	05/09/2022	Yes	Yes
1	some extend	Bad	08/05/2022	No	Yes

[dufoq3]

Hi @awsiya, or maybe something like this:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVHLDoIwEPyVhjMJpQjqyajxcdLEizGEQy0rEis1UBX/3i0EgkQPXrbN7szsdBqGlmvZ1gEKrCulYjzckUN9h1HG2ompkV1jC3UFAqWGzIBn3FTmOzQwFO8rZaNapOs51DXIwYeU7jFqjUUpQMp6ipYoa4iVIBYDp3jd84Kc0D1Zv5IUslRgb/uAnEtJlgDxkYuLsTnsaOxAwE2nKiM6B64hJk9choNpms+xK5SSBDJ1T86Tnq9OUEETVNfSr5jMG8b/JUu7n1HtqBjRGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Toprow = _t, QID1 = _t, QID2 = _t, QID3 = _t, QID4 = _t, QID5 = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Filtered Rows" = Table.SelectRows(#"Demoted Headers", each ([Column1] <> "Toprow")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    Ad_SortColumn = Table.AddColumn(#"Added Index", "Sort Column", each if List.Contains({0, "0"}, [Column1]) then -1 else [Index], Int64.Type),
    #"Sorted Rows" = Table.Sort(Ad_SortColumn,{{"Sort Column", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Sort Column"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    Renamed4htColumnDynamic = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){3}, "Date"}})
in
    Renamed4htColumnDynamic

[amustafa · Accepted solution]

let's say your sample survey data is in Excel table "Table1" then you can use Power Query to do some transformations steps as indicated in the M Code below. See the output.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Toprow", Order.Ascending}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Sorted Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"0", Int64.Type}, {"Was food Hygienic", type text}, {"Overall Feedback", type text}, {"27/02/2024", type any}, {"Reception treated well", type text}, {"AirCon cool enough?", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"27/02/2024", "SurveyDate"}, {"0", "Toprow"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"SurveyDate", type date}}, "en-GB"),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type with Locale",{"Toprow", "SurveyDate", "Was food Hygienic", "Overall Feedback", "Reception treated well", "AirCon cool enough?"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Toprow"})
in
    #"Removed Columns"

Output:

SurveyDateWas food HygienicOverall FeedbackReception treated wellAirCon cool enough?

8/5/2022	some extend	Bad	No	Yes
6/18/2022	Yes	Good	No	No
5/9/2022	some extend	Bad	Yes	Yes
6/25/2023	some extend	Bad	Yes	Yes
5/18/2022	Yes	Good	Yes	Yes
5/2/2024	Yes	Excellent	No	No
1/13/2024	No	Bad	some extent	Yes