How combine two tables from two spreadsheedts by Using integrated PowerPivot

[Ciria]

Dears:

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!

[Ciria]

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 🙂

[Vvelarde]

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

[sdjensen]

These 6 columns in the only columns in the 2 spreadsheets??

[sdjensen]

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.

[Ciria]

Dears:

Thanks a lot for your answers 🙂

I have to say that I have the same mindset than you about how append works. I thought when you append two tables, Powerbi just put one table after another.

What in my case is doing (I also try in Excel by using PowerPivot), is putting all the rows from Spreedsheet 1, and then all those from Spreendsheet 2, but not right after, but in the end of spreedsheet 1 and starting in the next column.

I hope this picture helps, sorry today I can't find the right words in English to explain 😞

Thanks!

[Ciria]

Dears:

Any support please?

I dont' know why Powerbi is doing this....

Thanks in advance,

[KHorseman · Accepted solution]

@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.

[Ciria]

KHorseman thanks!!

It was what you said, changing the column headers (using the same names in both files), and defining the exact number of columns in both files, Append function worked well 🙂

Great job!

Thanks again guys!