78 Data Exploration in Power Query
Managing columns
After connecting to a data source from Power Query and after having selected the table to which it is connected, it is best practice to reduce and delete all data that is not relevant for the preparation and transformation processes and therefore for reporting.
You have the possibility to choose the columns you want to work with, thereby reducing the amount of data involved. With this recipe, we will see how to quickly and intuitively select columns in order to speed up the data preparation process.
Getting ready
For this recipe, you need to download the FactResellerSales comma-separated values (CSV) file into your local folder.
In this example, we will refer to the C:\Data folder.
How to do it…
Once you have opened your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get Data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the FactResellerSales CSV file and open it. A window with a preview of the data will pop up; click on
Transform data.
3.Within the Home tab, focus on the Manage Columns section:
Figure 3.10 – Manage Columns
You have two possibilities to restrict the number of columns:
•Choose Columns—Click this to choose columns you wish to keep
•Remove Columns—Click this to remove columns you do not need
Managing columns 79
Choosing columns
You can choose columns you wish to keep with the following steps: 1. Click on Choose Columns:
Figure 3.11 – Choose Columns button
2. A view where you can choose the columns you want to keep will appear:
Figure 3.12 – Choose Columns window
80Data Exploration in Power Query
3.Remove the flag from (Select All Columns), type Sales in the search bar, and flag
(Select All Search Results):
Figure 3.13 – Choose Columns selection
4.Repeat the same step by selecting all columns containing Date in their name and click on OK; you will end up having 10 columns instead of 27.
The Choose Columns section is also aimed at browsing tables more quickly and finding the column you want to transform or enrich. If you click on Go to Column, a relative window pops up whereby you can change which column you end up selecting:
Figure 3.14 – Go to Column
While the Choose Columns step allows you to add a Power Query step and transforms your query, the Go to Column step is just a UI function for easy navigation and does not result in a query step.
Managing columns 81
Removing columns
You can also decide to do this the other way around and not choose columns to keep, but rather delete columns by completing the following steps:
1.Select the first three columns by pressing the Ctrl button and clicking on each in order to get the following view:
Figure 3.15 – Column selection
2.Expand the Remove Columns button and click on Remove Columns to remove the selected columns:
Figure 3.16 – Remove Columns button
If you had clicked on Remove Other Columns, you would have kept the selected columns and instead removed the others.
These flexible actions are useful because they allow you to choose how to remove unnecessary data and optimize the data preparation process. Useless and redundant data tends to slow down the entire data transformation pipeline, and it is better to discard everything that is not useful and strategic for data analysis.