promote 4th row as header

[sudhav]

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

[v-cgao-msft]

Hi @sudhav ,

You can consider using conditional columns with sorting to solve.

Suppoese we have a table like this:

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

Add a conditional column:

Sorting conditional column:

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

result:

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 · Accepted solution]

Hi @sudhav ,

You can consider using conditional columns with sorting to solve.

Suppoese we have a table like this:

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

Add a conditional column:

Sorting conditional column:

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

result:

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]

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'.

[sudhav]

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]

Please have a look at this similar post.

[Idrissshatila]

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

[sudhav]

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...