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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sudhav
Helper V
Helper V

promote 4th row as header

Hi Professionals,,,

 

I need to use my 4th row as header instead of "use first row as header" in PQ table.

 

how can i achieve it.. please give some ideas..TIA

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @sudhav ,

 

You can consider using conditional columns with sorting to solve.

Suppoese we have a table like this:

vcgaomsft_0-1674192587086.png

Again, we want to use the fourth record as the title.

Add a conditional column:

vcgaomsft_1-1674192666682.png

Sorting conditional column:

vcgaomsft_2-1674192848999.png

Use the first row as the headers and remove the conditional column.

result:

vcgaomsft_3-1674192990140.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYy5DcAwDAN3Ue3CVBzHKfNsIWj/NUIqxRHEgWCEwZo52ciwbGE72yQHWWVONnSF1vCSF+tNHvKWgT4wFPrA/K1+sBT68W6ZHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "A" then 1 else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Descending}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Sorted Rows", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"1"})
in
    #"Removed Columns"

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

7 REPLIES 7
v-cgao-msft
Community Support
Community Support

Hi @sudhav ,

 

You can consider using conditional columns with sorting to solve.

Suppoese we have a table like this:

vcgaomsft_0-1674192587086.png

Again, we want to use the fourth record as the title.

Add a conditional column:

vcgaomsft_1-1674192666682.png

Sorting conditional column:

vcgaomsft_2-1674192848999.png

Use the first row as the headers and remove the conditional column.

result:

vcgaomsft_3-1674192990140.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYy5DcAwDAN3Ue3CVBzHKfNsIWj/NUIqxRHEgWCEwZo52ciwbGE72yQHWWVONnSF1vCSF+tNHvKWgT4wFPrA/K1+sBT68W6ZHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "A" then 1 else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Descending}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Sorted Rows", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"1"})
in
    #"Removed Columns"

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

v-cgao-msft
Community Support
Community Support

Hi @sudhav ,

 

You can consider using conditional columns with sorting to solve.

Suppoese we have a table like this:

vcgaomsft_0-1674192587086.png

Again, we want to use the fourth record as the title.

Add a conditional column:

vcgaomsft_1-1674192666682.png

Sorting conditional column:

vcgaomsft_2-1674192848999.png

Use the first row as the headers and remove the conditional column.

result:

vcgaomsft_3-1674192990140.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYy5DcAwDAN3Ue3CVBzHKfNsIWj/NUIqxRHEgWCEwZo52ciwbGE72yQHWWVONnSF1vCSF+tNHvKWgT4wFPrA/K1+sBT68W6ZHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "A" then 1 else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Descending}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Sorted Rows", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"1"})
in
    #"Removed Columns"

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Smalfly
Responsive Resident
Responsive Resident

Hi @sudhav ,

 

is there information you need in the first 3 rows?

If not, you can first delete the top 3 rows, and then use 'first row as header'.

Smalfly_0-1674122528353.png

 

No, first 3 rows,I should not delete, is there any unique way to promote my nth row as header by keeping all the top rows as it is (without delete). please let me know... thankyou 

Smalfly
Responsive Resident
Responsive Resident

Please have a look at this similar post.

Idrissshatila
Super User
Super User

Hello @sudhav ,

 

check if this video works for your situation https://www.youtube.com/watch?v=7LJ0EgTc4rU&t=196s

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

 

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




In that video, first rows were deleted, I just want to keep all the rows and promote my nth row as header. please let me know any unique way.. please...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors