In this video, we'll see an overview of the different transformations available in Power BI Desktop. Let's get started. First, we'll navigate to the Home ribbon, click on Get data, click on More, click on File, textCSV, then click Connect. Navigate to your file, in this case we'll be analyzing the sales data, and click Open. When we connect to data we have the option to load or transform data. When we click on Transform Data, a new window called the Power Query Editor appears. With Power Query Editor, you can navigate, define and perform data transformation operations over an entire data source. On the left-hand side, you'll see a list of the different tables or queries in your model. In the middle, we see a preview of the different columns and values from our data source, while on the right-hand side in the Query Settings pane, you'll see the table name along with the applied steps or step-by-step transformations done within Power BI on the data source. On the top, you'll see the Home ribbon, along with other tabs which allow for transformation operations. If we hover our mouse over a particular transformation operation, we get a brief description of that operation. You'll find that when you wanna perform a transformation, there are multiple ways you can do that. Either by right-clicking on the column, or selecting from the Home ribbon and Transformation bar from the ribbon. For example, you can rename columns in a couple different ways. One is if you click on the column, when we see that it's highlighted here, if we double click in the title name, we can re-type the title here using the keyboard. Otherwise you can right-click on the selected column and click rename. And a third way of doing this would be to select the column, go to Transform and click on Rename. As you can see, there are multiple ways that you can perform transformations with data in the Power Query Editor. The Data source settings allows you to manage the settings of each of your data sources. Here, you can see the data source for each table within our data model. We can change that source and edit permissions or clear permissions. We can perform many transformations while in the Power Query Editor. Among those include the ability to choose or remove column. Here, under Manage Columns, under the Home tab, we can see that if you can choose which columns you would like, include in your data model. Similarly, you can choose to remove different columns within your table data source as well. If we don't want to, if we want to reverse that transformation, all we have to do is go to the Query Settings and Applied Steps and remove the last transformation. In addition to choosing and removing columns, we can choose and remove rows as well based off of different criteria. And we can perform transformations, such as splitting a column by a delimiter, by number of characters, positions, casing, or digit. We can group our data by a particular field, and we can change the data type of our data while selected on a particular column. And we can view the data type of each column right next to the column or the title of the column, the name of the column. We can use the first row as headers whenever we're working with text or CSV data or Excel. We can also replace values. This is particularly helpful when you have nulls in your data, and you want to replace those with a specific value. We can perform a merge or append query. A merge is similar to an Excel V lookup function or a SQL joint operation. While the append query is similar to a SQL union. In the Transform tab, we get additional operations that you can perform on your data. We can group data, we can use the first row as headers, we can transpose data, reverse rows and invert the table, count the rows or records within a table, change the data type, rename the particular column, replace values, fill a particular empty cell, pivot or un-pivot our data, turning rows into columns or columns into rows. Splitting our column by a delimiter, or characters, positions, casing, or digit. Formatting our data, such as changing it to all lower case, upper case, capitalizing each word, trimming or cleaning, or adding a prefix or suffix. And also extracting specific information from our data. There are many mathematical operations that we can perform on numeric columns. If we select a numeric column, such as unit price we see that we can get the sum, min, max, median, average, standard deviation count, or distinct count of that particular column. Or we can use mathematical operations, such as adding, multiplying, subtracting, dividing, as well as a number of other operation. We can perform some scientific, more complex operations against our numeric columns. Such as trigonometry or scientific. We can also do rounding on our numeric columns, as well as detect whether it's an odd or even number or it has a particular sign in front of it. In the Add Column tab, you'll see that you can add a column from an example, add a custom column using code to write a new column, invoking a custom function, writing a conditional column or indexing a column, duplicating a column, and working on text data, we can format that data or extract that data. Under the View tab of the Power Query Editor, we have different visibility settings on the Formula bar. If we click on Formula bar, here we see the different code that was written in order to produce the transformations that are happening behind the scenes. This code is written in a language called M, that's M for modeling. And if we expand this we get to see the different code written behind the scenes to pull in this data. Also, if we're investigating the quality of our data what we may find useful is column quality, column distribution and column profile. If we select on a particular column here, we can see those that are valid, those that are errors are empty. And then at the bottom, we see the distribution of the values, so we're getting more 310s in the product key. And we also see the column statistics to the left. This is helpful when cleansing data or trying to determine how to best cleanse the data for future modeling. The Help tab shows guided learning, documentation, training videos, meeting support, samples and galleries, as well as ideas for improving Power BI. After you're done with all your transformations, it's important to go to the Home tab and click on Close and Apply, so we ensure that the transformations that we performed against each of our data sets will show or reflect in the data model that we have for reporting. This concludes our lesson on navigating the Power Query Editor and transformations. Thank you for watching.
