Power query - misaligned column data after export from multiple worksheets

Pineapplecake

New Member
Joined
Aug 19, 2021
Messages
2
Office Version
  1. 365
Hi All - I have a file with multiple worksheets that I want to consolidate via power query into one big list. Each worksheet has consistent column names and order

However when loading the data , only one of the worksheets seems to load an extra column which causes all the other row data to misalign like the example below

Worksheet column names in the file (all the same)
Name | country | team | role

Power query exported columns:
Name | blank | country | team | role
Sheet 1 | <blank> | US | team 1 | BA
Sheet 2 | Canada | Team 2 | team 2 | <blank>

I can’t find what the issue is as all the worksheets are in order and same names and it's only for this one worksheet out of the 8 causing this problem. Would anyone be able to help? Thank you’
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Would you have in that specific file/sheet a merged cell? Power Query will "see" 2 columns, and create a columns with all blanks.
 
Upvote 0
Would you have in that specific file/sheet a merged cell? Power Query will "see" 2 columns, and create a columns with all blanks.
The file is work related so I'm not able to share.

However I found if I delete a column in my worktab and add it back in, the power query doesn't pick up the extra blank column so not sure what it is
 
Upvote 0
Hi, I did not ask to share, but to look for any merged cells.
You can share the PQ code for your second question though.
Would you have columns names that look alike but aren't? Trailing of leading spaces, capitals or lower case, typos,... Stuff like that. Do you a hardcoded definition of the columns to show inside the PQ?
Even if you have source tables where the columns are in a different order, PQ would still combine them correctly. Is the column header that matters, not the order.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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