Appending multiple tables & creating date from the text columns
One of the clients came up with a small project. They wanted to quickly analyze their sales performance in three countries for the past three years. The data file shared with me was not raw data, but a country-wise monthly summary report.
Below is one of the three tables:
The other two tables are for Germany and India.
Following are significant challenges with the data:
Data is in three separate tables
Yearly data stored in three separate columns, with a prefix “Sales.”
The month in a separate column, in text format.
We are using Power Query to compile and transform data under two minutes!
Step 1: Appending all three tables.
For this example, I would like to compile all the three tables in a separate table. Since all the tables have the same headers, our job is straightforward. We will use the Append Query function, which equivalent to Union in SQL queries.
Home > Append Query as New
In the Append Query window, select Three or more tables and add all the tables under Tables to append.
Click OK, and we get a new table with the data from all the three tables.
Right-click on the other three tables and uncheck the option Enable Load.
This step ensures only the table with all the records loads in the data model. Since we do not require the other three tables, we have excluded them from loading to the data model.
Step 2: Bring data for all the three years in one column
The sales data for three years are in three separate columns. For analysis, this is not helpful. What we need is all the sales value in one column and along with the subsequent years in another separate column. For this, we use the option unpivot.
Select the columns for the three years > Transform > Unpivot
For the next step, we need to extract the year value from the column. There is more than one way to do this. In this example, we are using the option Extract under Transform
Select the column > Transform > Extract > Last Characters
Since we need the last four characters, enter 4 in the Extract Last Characters:
Output:
Step 3: Transforming texts into a date format
Now the challenge is that we have two columns containing date components with column property as text. To meet our objective, we need one column with a date property.
Select the column Month and Year > Right-Click > Merge the column with space as delimiter
Transform the column from Text to Date
And our table is ready for analysis under two minutes.
