Forum Discussion
Solved
How to use non first row as column header
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 |
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
2 Replies
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 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
