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

Question

I have 2 sheets with dataSheet 1Product Group 1&nbsp; &nbsp; &nbsp;Customer&nbsp; &nbsp; &nbsp;Produc Group 2&nbsp; &nbsp; AmountA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Hiti&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Arg&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Van&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Bra&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20Sheet 2Product Group 2&nbsp; &nbsp; Produc Group 1&nbsp; &nbsp; Customer &nbsp; &nbsp;AmountVie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Chien&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30Tha&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Tu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp;Now I want combine 2 sheet to 1 sheet in Power query or Power Bi, How?Product Group 1&nbsp; &nbsp; &nbsp;Product Group 2&nbsp; &nbsp; &nbsp;Customer&nbsp; &nbsp; AmountA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Arg&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Hiti&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Bra&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Van&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Vie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Chien&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Tha&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Tu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp;Thanks for your help!!

tommartens · Answer

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

Reorder the columns in one of the queries
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

tommartens · Answer

Hey,
&nbsp;
I'm not sure what I do not understand.These are the steps:
1 - Import both sheets into Power Query&nbsp;
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:

&nbsp;
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". 
&nbsp;
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
&nbsp;
1st additional step - Manually reordering the column by dragging and dropping

&nbsp;
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).&nbsp;

&nbsp;
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.
&nbsp;
Regards,
Tom

nmhung49 · Answer

TomMartens&nbsp;I can not Reorder the columns in one of the queriesCan you help me? with example&nbsp;Example.xlsx&nbsp;P/s: I am not use create Table to do It&nbsp;&nbsp;&nbsp;

tommartens · Answer

Hey,
&nbsp;
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)
&nbsp;
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):
&nbsp;

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

nmhung49 · Answer

TomMartens&nbsp;I know, but the data is incorect in power query in the columnsI want picture ex1 to picture ex2

Forum Discussion

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

6 Replies

Related content

Tables combine

Power Query Combine Multiple Files From Folder

Combining Data from Different Semantic Models

Combine values from multiple rows

How to Combine Multiple Goal Achievement Measures with Different Visual-Level Filters