Power Query Issues

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
1,015
Office Version
  1. 365
Platform
  1. Windows
I'm new to PQ so please bear with me. I made changes in the table headers in the source data and now my data has shifted down as seen below. Row C was my original header. What is going on and how do i correct this? This is not the first time this has happened and I am not understanding what is going on. Any help is greatly appreciated.

v2023.10.xlsm
ABCDEFG
1Column1Column2Column3Column4Column5Column6Column7
2
3MaskedPartNumberPartNumberIndoor_OutdoorProduct_TypeProduct_SubgroupApplication_SubgroupSize
4TBD 1/2-EMT Conduit 1/2-Inch x 10'1/2-EMTIndoorMaterialsConduit and Fitttings
5TBD 2-Screw EMT Set Screw Coupling 3/4-Inch249032IndoorMaterialsConduit and Fitttings
6TBD Duplex AC/Flex Connector for MC, 3/8"EGS DC-5500IndoorMaterialsConduit and Fitttings
7TBD Insulated Throat Straight Duplex Connector 3/8-Inch Snap2IT3838ASTIndoorMaterialsConduit and Fitttings
Materials
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
When you loaded your source data, you may not have checked the box that indicated that the first row of the range/table were headers. If this has happened then highlight the first row and delete it. Then promote the new first row which contains your headers to header. Using your sample

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"
 
Upvote 0
Alan - thank you. I did not check that box off, so to avoid any issues going forward, if i wanted to make those changes now, on a number of source data sheets without having my data move, how would i make the first row into a header?

please see the attached image. this is what my sheets look like.
 

Attachments

  • headers.png
    headers.png
    240.7 KB · Views: 19
Upvote 0
On Transform Tab, select Use First Row as Headers. However, in your image, it appears you already have headers in place.
 
Upvote 0
is it possible that whathappened in my first image occured even when headers were in place? I'm fairly confident I connected all of the data the same.
 
Upvote 0
If you don't click on the checkbox, then the image in your first post is the result.
 
Upvote 0

Forum statistics

Threads
1,223,317
Messages
6,171,419
Members
452,402
Latest member
siduslevis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top