Forum Discussion
How combine two tables from two spreadsheedts by Using integrated PowerPivot
Dears::smileysurprised:
I have a excel file, daily updated (adding records, or adding results or further information for those records), divided in two spreedsheets.
The first spreedsheet takes Product Type A and the second Product Type B. Because of the European regulation we cannot combine in just one single spreedsheet all the records (audits)
Column structure and headers are quite similar, in fact you could use them independently. This means that in Column B both spreedsheet contains the "Item code", Column C the "Description", and the "Results" are in Column G in both cases :-)
My attempt was to try to mix/combine both tables using either Merge or Append, but in this case what Powerbi does is to put the records on columns, this is after the last column from the first spreedsheet, on the top right.
But I would like to do is to find the way to put one after the another, removing before in "Edit Queries" the header from the second one, having a single list with multiple records in order to get data from the whole list.
I could do this manually of course, but as I said, this file is updated daily, so the aim would be to create, somehow, a way to do this in Powerbi directly having automatic update.
I'm looking forward to hearing from you.
Thanks!:smileyhappy:
Ciria it's hard to tell what's going on when there's nothing in your screenshot but null values. Do all the columns in table 1 have the same names as all of the columns in table 2? If the columns don't have matching names, Power Query has no way of knowing which column from table 2 is supposed to match up with which column of table 1, so they will be merged as a new set of columns with null values where the two tables overlap.
8 Replies
Here a picture that shows what I said. As you can see, because of the European regulation, we have to provide different codification to the internal batch, based on the product group/family.
If product belongs to Family A, it goes to the first spreedsheet, on the contrary, if it belong to Family B, it goes to the second spreedsheet.
Thanks for your support :-)
These 6 columns in the only columns in the 2 spreadsheets??
I just created to similar Excel sheets to what you posted.
I first added Excelsheet 1 and the added the 2nd and then used append to load data from sheet 1 into the same table as sheet 2 after this you can just hide table 1 in your model.
Edit Query--Append:
They put your 2do file's rows below the last row of spreadsheet1
Example:
File 1
ID Code
1 A
2 B
3 C
File 2
ID Code
4 D
5 E
6 F
Step 1: Edit QueryStep2: AppendStep 3: Ready
