Manually Assign Certain Rows as Column Headers

[2019]

I have attached a snapshot of the table I have it’s two images but in reality, it’s one table.

It’s set up in excel sheet in this way and I do not want to change it in excel sheet rather than that I want to do the changes in power bi

I want to assign certain rows as column headers as I have highlighted in yellow colors To be something like this:

DS	SOR for Drilling	SOR for Well Services	LRC Issued for Pad	Civils Complete for Drilling	Rig Spud	Rig Release	Etc…	POP	Comments	Etc…
Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data

ps: you can download the sample PBIX file from PBIX File 

[edhans]

A few things:

1. You are using an older version of Power BI for Report Server, and had I been able to make changes, I am not sure I could have shared with you.
   1. 
2. You didn't share the data. This is link to file.xlsm on your computer, so I cannot work with it. Share the Excel .XLSX file. Don't share an XLSM file. I will not open a file with macros in it.
3. By just looking at your screenshots, the solution might be fairly simple.
   1. Select Column25, then on the Power Query Transform ribbon, select Fill Down.
      1. 
   2. That will put POP in the row below it. Now it seems that row 3 has all of the necessary data based on screenshots.
   3. On the Power Query home ribbon, select the Remove Rows dropdown, and remove the top 2 rows
      1. 
   4. Finally, on the Home rbbon, "Use First Row as Headers"
      1. 

If that isn't waht you need, share your data in an XLSX file. I don't need Power BI. I can write the query in Excel's Power Query tool, which you can then just move to Power BI later.

[2019]

@edhans 

Thank you for your reply

with regard:

1.  older version of Power BI for Report Server (my company are using Jan 2020 version in their Report Server at the moment and i can't update it).
2. you can download the .XLSX file File2 ,please read note sheet
3. (a and b) i used your method for Column25 and it work but what about the Column1-7 and Column 32-36

I am trying to clean tha data in power Bi where i should have the correct column headers and leave the excelsheet file as the same structure (other people updateing it and they are used to the look and feel of that excelsheet file)

[edhans · Accepted solution]

This is much more difficult than it should be because of how the file is laid out, but I get your users are comfortable with that structure. A true Excel Table would make this a non-issue.

Paste this code into a blank query in Power Query.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive - eHansalytics\Downloads\File2.xlsx"), null, true),
    #"MP Date_Sheet" = Source{[Item="MP Date",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"MP Date_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",5),
    #"Filled Down" = Table.FillDown(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36"}),
    #"New Header" = Table.LastN(#"Filled Down", 1),
    #"Data Rows" = Table.Skip(#"Changed Type", 5),
    #"New Table" = Table.Combine({#"New Header", #"Data Rows"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"New Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

You'll need to change the Source line to your file path on your PC.

Here is what I did:

1. I only did this on the MP table as an example.
2. I kept the top 5 rows as those were the header rows. I didn't do anything to make this dynamic to find the first data row so you might have to change this per tab.
3. I then filled down on all columns., then I removed all but the last row. This will be my new header row in a few steps
4. I kept the data rows, which is all but the top 5 rows. Note this points back to the #"Changed Type" step, not the previous step.
5. I combined the New Header row with the Data Rows. Now I have one nice table.
6. On the Home Ribbon, pressed Used First Rows as Headers.

You will have to tweak the M code in the formula bar for this to work. The UI buttons will not let you jump around in steps like I am doing here.

[2019]

@edhans 

Thank you so much for the help you gave me, I greatly appreciate the assistance you have provided me.

[edhans]

Great @2019 - hopefully enough info for you to proceed. If you have any sticky issues with a particular worksheet, let us know.

[2019]

Dear@edhans 

Now I have another challenge which I tried to accomplish but without any success,

I want to combine 5 tables

(see below)

into new one table where I select which column to bring

(see below)

under one condition if a certain column value equal to certain text

(see below)

[Greg_Deckler]

@ImkeF @edhans