1. Merging and appending queries
Let's have a look at some table shaping operations in Power BI.
This time, we only have our datasets split up by year and need to append them using Power Query. We'll start with the one from 1978.
Selecting Transform Data brings us to Power Query. You can see that the first row is superfluous again, and after deleting it, we can promote the new first row as the headers. To append the second table, select New Source and then Text/CSV. This table comes from the year 1979. Select OK since you're already in Power Query. I need to perform the same operations: remove the top row and promote the new top row as headers.
Now that I've done this, I have two datasets that are actually equivalent. To create a new table with the 1978 and 1979 tables appended, you can select Append Queries in the Combine section, and then choose between Append Queries or Append Queries as New. The former will replace the current table with the appended one, the latter creates a new table where the two tables are merged together. The new table, called Append1, now contains data from two tables, but you could append as many tables as you want. Let's rename Append1 to 1978-1979 for clarity.
For demonstrating purposes, let's break out Establishment age code into its own table. To do that, you can copy this 1978-1979 table by right clicking and selecting Duplicate. For this new, duplicated table, I'll use the acronym EAC and remove all columns but Establishment age code and its meaning. This table now contains a lot of duplicates which you can remove by right clicking and selecting Remove Duplicates. As a result, the EAC table is now a reference or lookup table. We could remove the meaning of Establishment age code in our appended table and use this column as the key to the EAC table.
The last thing that I want to demonstrate, is how to extract values from a column. There are different options to play with, including ways to extract data based on the number of characters, their position, or a specified delimiter.
Suppose we just want to keep the last two characters from the ID column. In the Transform menu, under the Extract option I'm going to choose Last Characters. Just keep the final two characters, and we're good to go! To apply all this and return to Power BI, just click Close & Apply in the home menu.
2. Let's practice!