We will be discussing on how to work in query editor. It would be good to refer my previous article Power BI Desktop – Connecting to Data (part 1/3) to get a better understanding of the example we are using here.
So, time to look at the data in the query editor!
Fix the Headers
We are in the stage of data preparation right here and we see that the column names are not good. We see that a better column name would be the second row instead.
To do this you need to first delete the first row. In the Home tab click Remove Rows, Remove Top Rows. Specify 1 (row 1) and click Ok.
Now, click Use First Rows as Headers to set the columns. With that we are finished with the work on our rows!
Fix the Columns
You can select a column by clicking on the header and then navigate with the LEFT and RIGHT arrow keys on the keyboard.
We can keep all the columns but we don’t think we would need “Country/Series-specific Notes” column. So, we can go ahead and delete that. For that you can simply select the column and click Remove Columns on the Home tab. Similarly, you can delete some last columns such as “Column 14“.
Now you will notice that the are some year columns, we think they are not displayed in the best way!
Wouldn’t it be better if we changed simply into two columns, one column displaying all the years in all the corresponding rows of that column and the second column showing the GDP data!
To do that, select the first-year column, press and hold the SHIFT button on the keyboard and select the last column. This will select all the year columns. Now, on the Transform tab click Unpivot Columns.
Now you will notice that we simply turned, multiple columns into one single column where all the years are displayed in the rows of that column.
You can now rename the column names, by double-clicking the columns.
Now we do not want to load any unwanted rows in our data model, for example we can filter out all the non-applicable GDP data from the set.
Now we have a clean data, but the values in GDP column are formatted as text. It would be good to change the format to Decimal number. Lets go ahead and do that as well.
You can also, select multiple columns and click Detect Data Type in the Transform tab.
Now we have finished our work in the query editor, in this data preparation step. The next step now is to load this clean data into the actual data model and create visualizations.
In the following topics, check out what we can do with this data: