Power BI – Combine Data
Combining data means connecting to two or more sources, shaping them, and then consolidating them.
Example for Combining Data
To facilitate further analysis and queries, it would be beneficial to have information about different states. However, the majority of available data uses a two-letter abbreviation for state codes rather than the full state name. In order to link state names with their corresponding abbreviations, a solution needs to be found. Luckily, there is another publicly available data source that provides this information, but it requires significant manipulation before it can be integrated with our retirement table.
Steps to Shape this Data
- Select New Source > Web from the Home ribbon in Power Query Editor.
2. Enter the address of the website for state abbreviations, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations, and then select Connect.
Website content is displayed in the Navigator.
3. Select Codes and abbreviations.
To get the data into shape, follow these steps
- You can eliminate the top row which is a result of the web page’s table creation process and is unnecessary. To do so, go to the Home ribbon and choose the Remove Rows option, then select Remove Top Rows.
You can specify how many rows you want to remove in the Remove Top Rows window.
2.The bottom 26 rows should be removed. We don’t need to include these rows, as they are U.S. territories. Select Remove Rows > Remove Bottom Rows from the Home ribbon.
3. The RetirementStats table does not contain information for Washington DC, so we need to filter it out. To clear the Federal District checkbox, select the Region Status drop-down menu.
4. To eliminate unnecessary columns, select the relevant column and hold down the CTRL key while selecting the other columns to be removed. Since we only require the mapping of each state to its official two-letter abbreviation, we can remove several columns. To do so, navigate to the Home tab on the ribbon and choose the Remove Columns option, then select Remove Columns.
5. You can rename the columns and table using two different methods. One way is to select the relevant column and either right-click and choose Rename, or go to the Transform tab on the ribbon and select Rename. Both options are indicated by arrows in the image; you only need to use one of them.
6. The columns should be renamed State Name and State Code. The Query Settings pane can be used to rename the table. StateCodes is the name of this table.