cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
2019 Helper I
Helper I

Manually Assign Certain Rows as Column Headers

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

 

1.PNG2.PNG

 

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Manually Assign Certain Rows as Column Headers

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

Re: Manually Assign Certain Rows as Column Headers

@ImkeF @edhans

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User II
Super User II

Re: Manually Assign Certain Rows as Column Headers

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. 2020-04-09 07_15_27-.png
  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. 2020-04-09 07_21_28-Untitled - Power Query Editor.png
    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. 2020-04-09 07_23_26-Untitled - Power Query Editor.png
    4. Finally, on the Home rbbon, "Use First Row as Headers"
      1. 2020-04-09 07_24_12-Untitled - Power Query Editor.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
2019 Helper I
Helper I

Re: Manually Assign Certain Rows as Column Headers

@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)

Super User II
Super User II

Re: Manually Assign Certain Rows as Column Headers

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
2019 Helper I
Helper I

Re: Manually Assign Certain Rows as Column Headers

@edhans 

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

Super User II
Super User II

Re: Manually Assign Certain Rows as Column Headers

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
2019 Helper I
Helper I

Re: Manually Assign Certain Rows as Column Headers

Dear@edhans 

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

I want to combine 5 tables

 

(see below)

22.PNG

 

into new one table where I select which column to bring

(see below)

23.PNG

 

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

(see below)

 

24.PNG

Helpful resources

Announcements
June 2020 Community Challenge: Can You Solve These?

June 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors