Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
awsiya
Frequent Visitor

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?

ToprowQID1QID2QID3QID4QID5
1YesGood18/05/2022YesYes
1some extendBad25/06/2023YesYes
1NoBad13/01/2024some extentYes
1YesExcellent05/02/2024NoNo
0Was food HygienicOverall Feedback27/02/2024Reception treated wellAirCon cool enough?
1YesGood18/06/2022NoNo
1some extendBad05/09/2022YesYes
1some extendBad08/05/2022NoYes
      
      
      
      
      
      
1 ACCEPTED SOLUTION
amustafa
Super User
Super User

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @awsiya, or maybe something like this:

 

Result:

dufoq3_0-1709214900518.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

amustafa
Super User
Super User

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors