question

qdras-4071 avatar image
0 Votes"
qdras-4071 asked Lz-3068 answered

Power query fills the rows in first column with null values after first file

I'm trying to combine multiple *.xlsx files with power query. Problem is that after the first file it fills rows in the first column with null value. All the files have the same structure.

113329-the-files.png

Here is the problem.
In the first file it is OK

113380-1.png

with the second file is the problem

113442-2.png


power-query-not-supportedoffice-excel-itpro
the-files.png (132.9 KiB)
1.png (148.9 KiB)
2.png (147.8 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @qdras-4071

I carefully looked at your pictures but couldn't detect an obvious reason
Could you upload & share (with OneDrive or any other file sharing service) 2 files please?


0 Votes 0 ·

@qdras-4071
Thanks for sharing that made it easy. I think I see where the problem comes from. I need to double-check. IIn the meantime could you confirm you use method: Data > From File > From Folder to combine the files?

0 Votes 0 ·
Show more comments

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@qdras-4071

Problem
Mainly comes from the fact the the From File > From Folder wizard does a Promote Headers on the file taken as example and in your case, on the 1st row, column1 it's always different. Consequently, when the 2nd, 3rd... sheet is combined the correponding column doesn't exist => nulls
Please don't conclude the wizard bugs. It does a very good job but of course it cannot handle ALL existing possibilities. Note that you would never encountered this issue if you has used Tables instead of Sheets

Option1

PIC

  • In query "Transform Sample File" delete step Promoted Headers

  • In query "CZ" (for me because my Folder is named CZ) delete step Changed Type

  • Rename the columns as you want

Option2
Use the custom query I put together for you in this workbook (to download) where you will have to change the folder path in step Source of query CombinedSheetsDreviny

NB: I have no idea where column8 comes from as it doesn't appear to exist in your files. I kept it just in case. It'll be easy for you to delete it as a step in query CombinedSheetsDreviny

Any question let me know


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.