Using power query to combine multiple files that have a different amount of columns

[ESACAWIP]

Example:

Does anyone know how to combine multiple files that contain a different amount of columns with power querry?
-Column names that are the same and should all line up
-Some spreadsheets have additional columns (e.g. File 3, Column "Education 2"), which the other spreadsheets do not have
-I can not manually add or rearrange columns in the spreadsheets as I have hundreds of files, with each file containing hundreds of columns
-For the final output / result, I do not need the columns in any specific order. I just need to preserve all the data


Can someone point me in the right direction? When I tried to combine the worksheets, I ended up missing columns for some reason.

	Name	Age	Work History 1	Education 1
File 1	John	17​	Toys R us	Jeffersion High School
	Name	Age	Work History 1	Work History 2	Education 1
File 2	Paul	19​	OfficeMax	McDonalds	Adams High School
	Name	Age	Work History 1	Education 1	Education 2
File 3	Steven	31​	Costco	Douglass High School	Stevens School

Output:

	Name	Age	Work History 1	Education 1	Work History 2	Education 2
File 1	John	17​	Toys R us	Jeffersion High School	Null	Null
File 2	Paul	19​	OfficeMax	Adams High School	McDonalds	Null
File 3	Steven	31​	Costco	Douglass High School	Null	Stevens School

 

[alansidman]

Load each table to the PQ Editor. Close and Load each to a connection. Then combine them in a new append query.

let
    Source = Table.Combine({Table1, Table2, Table3})
in
    Source

Book2
	A	B	C	D	E	F	G
4	Column1	Name	Age	Work History 1	Education 1	Work History 2	Education 2
5	File 1	John	17	Toys R us	Jeffersion High School
6	File 2	Paul	19	OfficeMax	Adams High School	McDonalds
7	File 3	Steven	31	Costco	Douglass High School		Stevens School
Sheet1

 

[Alex Blakenburg]

I have hundreds of files, with each file containing hundreds of columns

Based on this comment, I don't think @alansidman's suggestion is going to be viable.
I suggest you have a look at Wyn Hopkins 9 min video here.
Maybe set up the code with only a few files in the folder before running it against all your files.

 

[ESACAWIP]

Based on this comment, I don't think @alansidman's suggestion is going to be viable.
I suggest you have a look at Wyn Hopkins 9 min video here.
Maybe set up the code with only a few files in the folder before running it against all your files.

It seems like this video is exactly what im looking for but when I followed the steps exactly, it always crashes my excel (even when combining just 2 files)

Each file has around 2000 columns though so maybe thats why

 

[Alex Blakenburg]

What file type (file extension) are the files you are trying to import ?
At what point is it crashing ? Before after the Preview screen ?
Is your Excel 64 bit ?

I am not clear on what happens when you blow the 3,000 Query Editor Pane limitation and you would do that with 2 rows of data.
Power Query specifications and limits in Excel - Microsoft Support

 

[ESACAWIP]

The file type is csv. In the video, it walks me through how I can grab all the headings (column headers) from all files rather than 1 example file. After that, I used the list.union function to keep only unique headers instead of duplicates. (=Table.ExpandTableColumn (ReadyToExpand, "Transform File", HEADINGS))

^When I expand using this list, that is when the preview screen gets stuck in this infinite loading cycle. I hit load into table anyway and at around 20 minutes, it will crash my computer.

I am using office 360 / 64 bit on a fairly new computer.