In the previous post, we have understood how to load an excel dataset. In this post, we are going to go through a process of loading an unstructured excel data and converting them to a proper structured data.
When we use the term “structured data”, we mean by it should be report friendly at the same time, it should help me to derive to a meaningful decision out of that data.
We are going to see few options available in the Power Query Editor window which will help us to transform this unstructured data to a properly structured data.
We are going to start from the point, considering the data has been loaded. If you came to this post directly, request you to first read my previous post which speaks about loading excel data to Power BI desktop.
Now let’s start our journey..
Step 1: We need to go to Power Query Editor Window. In “Home” tab, under the “External Data” group click on the drop down “Edit Queries”, choose the option “Edit Queries”
Step 2: The previous step will take us to Power Query Editor window. Your screen would appear like screenshot pasted below
Step 3: In our dataset, we need to remove unwanted rows. We are going to first transform feature called “Remove Rows” under “Reduce Rows” group in “Home” tab.
Step 4: In our scenario, we need to remove top 3 rows, so we are going for the option “Remove Top Rows”. Now enter the number of rows as 3, as we need to remove only 3 top rows and click OK.
Step 5: First row in the dataset is our headers, we need to convert them from data record to header rows. To achieve this we are going to use next transform feature, “Use First Row As Headers” in “Transform” group under “Home” tab.
Step 6: Converting the month columns to rows in order to a comparative analysis. In order to achieve this we are going to use the third transform option called “Unpivot Columns”.
Step 7: Now select all the month columns, go to “Transform” tab, click “UnPivot Columns” option under “Any Column” group.
Step 8: We got two columns now, one with a name “Attribute” and other one with a name “Value”. We are going to rename them.
You can do it by simply right clicking on the heading and choose the option “Rename” or you can simply press “F2” key. We are going to rename them “Month” and Other column “Sales”
Step 9: Last part of our work, removing unwanted column from our dataset. In this dataset, the “Grand Total” column is not relevant after unpivot is done. To achieve this we are going to use the fourth transform feature called, “Remove Columns”.
Step 10: Select the “Grand Total” column in the dataset, click on the “Remove Columns” in the “Manage Columns” group under the “Home” tab.
Now what we have is a perfect dataset which I can take it further for my analysis work or a visualization requirement.
Step 11: To save and load the transformed data to power BI, you need to close Power Query Editor window and return to Power BI desktop window. To do it, click on “Close & Apply” option under the “Close” group in “Home” tab.
Additional Tip: All the transform steps are captured under the “Applied Steps”, any time you can revert to any point in our data by simply deleting the particular step.
In the next post, we shall discuss more on Data transforming options. I hope this post gave you some idea about using basic transforming options in Power Query Editor