Excel Power Pivot combining multiple sources into column

Question

I have multiple similar data files with a large overlap of similar rows. I'd like to combine them so that a given column from each set appears in a distinct column in a new table. Essentially this is very similar to a standard pivot table where the source is a column field and the values of the field are those of the original files where present.

So for 2 source files:

File 1

and File 2

I'd like to end up with:

So all the common data is in the row and there is one column for each file containing the "Status" (or blank if that row isn't present).

I want to have this as a data source that I can then pivot. Is this possible? I know how to combine the files into a single source using Get Data -> From Folder and I know I can then pivot that data, but it doesn't get me to the final solution above.

I think you may need to create two separate queries and then merge the two queries. You're problem will be creating a unique key to do so. If all the names are unique you can create a custom field which joins First name and Last name. You use this unique key to merge your queries. This will put the Status from each file on the same row. — ACCtionMan

Ben Ben · Accepted Answer · 2020-04-12T04:30:48

Assuming you've got 2 separate queries bringing in data from the 2 source files as listed above, first step would be to add a 'File' column to each of them ie Table.AddColumn(#"Previous Step", "File", each "File 1/2/3 etc", type text) , ie so you end up with: and

Then Append the 2 adjusted tables to give you this

Select the 'File' column, go to Transform => Pivot Column and in the pivot window choose 'Status' as the Values column and Don't Aggregate as the Aggregate Function

Which gives you your desired result

Excel Power Pivot combining multiple sources into column

1 Answers