transform sub heading in power query

Well, below are the steps that you might want to follow to achieve your goal. 

1. From the Data tab select "From table/Range" (assuming you've got the Excel sheet open)
2. Power Query window will be opened, now select the column that has the subheading, and from "Transform Tab" click "Unpivot" 
3. Now use "Use first row as header" option and then rename the "Attribute" column to a proper name. 

I am adding a GIF for your better understanding. 



Follow same technique for the rest of your subheader in the sheet and then use "Append" queries to append the tables as your table structure is same. 

Let me know if you have any further questions. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.    

lalithab 

let
    Source = Excel.Workbook(File.Contents("full_path_to_file"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sheet1_Sheet, each ([Column2] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Category] <> "Category"))
in
    #"Filtered Rows1"

Hi, lalithab 

Did you checked the solution I provided? 

Did it helped in any way? If yes, appreciate the kudos. 👍

if you find my reply helpful, accept this answer as solution. 

thanks