Combine Data with Different Columns on Multiple Sheets into a Single Sheet

[nmhung49]

I have 2 sheets with data

Sheet 1

Product Group 1     Customer     Produc Group 2    Amount

A                               Hiti              Arg                       10

B                               Van             Bra                         20

Sheet 2

Product Group 2    Produc Group 1    Customer    Amount

Vie                               C                      Chien                 30

Tha                              D                       Tu                      40

Now I want combine 2 sheet to 1 sheet in Power query or Power Bi, How?

Product Group 1     Product Group 2     Customer    Amount

A                               Arg                       Hiti                       10

B                               Bra                        Van                      20

C                               Vie                        Chien                   30

D                               Tha                       Tu                        40

Thanks for your help!!

[TomMartens]

Hey,

Import the sheets using Power Query, this will lead to two queries and then:

1. Reorder the columns in one of the queries
2. Use the Append operation in the Combine ribbon of the Home Menu:
   https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

Regards,

Tom

[nmhung49]

@TomMartens 

I can not Reorder the columns in one of the queries

Can you help me? with example

Example.xlsx

P/s: I am not use create Table to do It

[TomMartens]

Hey,

I assume you already imported both sheets into Power BI Desktop Power Query.

From Power BI desktop open Power Query (select Edit Queries from the Home menu)

Select one query, mark the query you want to move to another place, drag the column using the mouse (you have to do the on the column name):

As the manual reordering is "recorded" as a transformation step, it will work even if you refresh the data.

Regards,

Tom

[nmhung49]

@TomMartens 

I know, but the data is incorect in power query in the columns

I want picture ex1 to picture ex2

[TomMartens]

Hey,

I'm not sure what I do not understand.
These are the steps:

1 - Import both sheets into Power Query 

I imported both sheets from your sample xlsx that you provided. After that I have to queries in Power BI, please make sure that for both queries the 1st row has been "promoted" to column headers, if this is not the case use this step:

2 - Reorder the columns in of the tables manually (just drag and drop)

3 - Append one table to the other

Here you will find my pbix file. Please open Power Query and have a closer look at query "Sheet2".

Here are step 2 and 3 in more detail.

The red rectangle marks the step that have been executed/created by Power Query automatically during the import.

The green rectangle marks the steps that I added manually afterwards

1st additional step - Manually reordering the column by dragging and dropping

2nd additional step - Append query/table Sheet1 to query/table Sheet2.
This is the Append command that I'm referencing:

If, for whatever reason you want to append query/table "Sheet2" to query/table "Sheet1", just select query "Sheet1" and execute the Append command here.
Performancewise I recommend to append the smaller table (less rows) to the larger table (more rows). 

The final step (at least until now) creates the table in your latest post, except the reordering, but basically this is due to how the tables are appended.

Regards,

Tom

[nmhung49]

Thanks @TomMartens 

I know, Currently, I'm doing it this way without reordering the column, but when Sheet added, the data doesn't automatically update, I have to repeat the following steps

Step1

Step2

Example pbx

Data xlsx

I want when the sheet is added, the data will automatically update to the table All, No repeat step1 and step2