Clean and transform data
Whether a Power BI content developer accesses one data source or many, it is possible that the data might need modeling before it can be used effectively in a report. Data modeling—also called shaping or transforming data—is a term that can refer to a variety of tasks, including the following:
■ Modifying data types
■ Removing rows or columns
■ Renaming tables, rows, or columns
■ Splitting columns
The purpose of data modeling is to select and arrange the data accessed from the source so that it suits the visualization the developer intends to create. As always, modeling the data in Power BI does not change the original data in the source, so splitting, removing, and renaming rows and columns has no effect on the original data.
Modifying data types
One example of a data transformation is to modify the data type of a column. When a developer accesses a webpage as a data source, the resulting table might contain numerical values that Power BI Desktop sees as text. For example, the Overall rank column in Figure 3-42 is labeled as text (as shown by the ABC tag in the column header). Even though the values appear to be numbers, the Power Query Editor reads them as text and cannot use them in mathematical calculations.
FIGURE 3-42 Text column in Power Query Editor
By right-clicking the Overall rank column and selecting Change Type from the context menu, or by selecting the Data type drop-down list in the ribbon’s Transform group, the developer can choose Whole Number to convert the column to numerical values that the Power Query Editor can use in mathematical operations (as shown by the 123 tag in the column header in Figure 3-43).
FIGURE 3-43 Whole number column in Power Query Editor
After the developer changes the data type, that modification appears in the Query Settings pane in the Applied Steps box as Changed Type. To undo the change, the developer can simply delete the Changed Type transformation. Each transformation a developer applies to the data is added to the Applied Steps list for later manipulation, if necessary.
Need More Review? Using Power Query Editor
For more information on using the Power Query Editor, see https://docs.microsoft.com/en-us/power-query.