Forum Discussion

awsiya's avatar
awsiya
Helper I
2 years ago
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 ...
Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    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/2022some extendBadNoYes
    6/18/2022YesGoodNoNo
    5/9/2022some extendBadYesYes
    6/25/2023some extendBadYesYes
    5/18/2022YesGoodYesYes
    5/2/2024YesExcellentNoNo
    1/13/2024NoBadsome extentYes

     

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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