Power Query, which Power BI relies upon for data load and transformation first appeared in Excel. It enables one to automate changes to data sets through filters and other modifications each and every time data is loaded to a spreadsheet dependent upon a Power Query.
It also enables a user to join two separate spreadsheets as if they were working with tables in a database.
The following walks through the joining two tables in Excel, using Power Query.
The process for Merge and Append are essentially the same. These steps relate to Merge.
Merge = Combines two or more files based on one or more matching columns so that their shared data appears on one row. In this example, starting with 1085 rows, and another with 1082 rows, we will end up with 1085 rows.
Append = Simply combines (add rows from one to another) two or more files and matches columns where they have the exact same name. In this example, starting with 1085 rows, and another with 1082 rows, we would end up with 2167 rows
1) In Excel
a) Open a Blank Workbook
b) Choose the Data menu
i) Choose Get Data
(1) > from File
(2) > from Excel Workbook
(a) Select a file from the window that opens
(b) In the subsequent window, click the appropriate tab from that Excel file
(c) Choose Transform Data
(i) Power Query Editor window opens
2) In Power Query Editor, in the New Query section
a) Choose New Source
i) > File
ii) > Excel Workbook
(1) Select a file from the window that opens
(a) In the subsequent window, click the appropriate tab from that Excel file, and click OK
(b) Two reports now appear in the Queries pane (left)
b) In the Combine section,
i) Choose the drop down next to “Merge Queries”
ii) Choose Merge Queries as New
(1) A new Merge window opens, with one of the Excel files already chosen
(2) Choose the other Excel file from the drop-down in the middle
(3) Begin choosing which data columns to match on
(a) Click the column name from the top sample, then click the matching column name in the bottom sample. Both are highlighted when selected.
(i) Choose columns that contain the exact same information. Both tables should contain matches, though not all will match. In this example, the match is between “IDF Form ID” and “Individual Data form ID,” because they will contain the same data.
1. If matching more than one column, CTR+Click the second column
(4) Choose the Join Kind at the bottom.
(a) If unsure, choose “Full Outer” to keep all data from both Excel sheets
(5) Click OK
(a) The Merge window disappears, and a new table appears in the Queries in the left panel. This is file containing the merged files.
(6) Click Close & Load
(a) The window returns to the initial Excel Workbook and loads the merged files.
3) In the Excel Workbook, the right panel will highlight which file is currently being viewed
No comments:
Post a Comment