Hello and welcome. In this video, you'll look at the importance of transforming and cleaning data. So get ready, as you are about to learn even more about the world of data. Have you ever had to transform data that is in the wrong format or data that contains extra data? Well, to help you transform your data, Power BI Desktop gives you the Power Query Editor tool. Curious to see how this works? You simply use your stored data to create reports in Power BI Desktop. Once you've done that, you can publish your reports to Power BI service that you can then share via browser or mobile apps. This will help you shape and transform your data so that it's ready for your models and visualizations. So what are you waiting for? Let's launch the Power Query Editor. To begin, you can select Edit from the navigator window. Alternatively, you can also launch Power Query Editor directly from Power BI Desktop by using the Transform Data button on the Home ribbon. After loading your data into Power Query Editor, this screen will appear. Let's look at the various functionalities on this screen. The active buttons in the ribbon enable you to interact with the data in the query. The first one to look out for is data source settings. On the left pane queries are listed and available for selecting, viewing, and shaping. Here you will be able to see one query for each table or entity. The center pane allows you to view data from the Selected Query that is displayed and available for shaping, and finally, the Query Settings window lists the queries, properties, and applied steps. Next, you learn how to transform data. To transform your data, you will be working from the center pane in the Power Query Editor tool. You'll notice that when you right-click a column, the available transformations will be displayed. Examples of the available transformations include removing a column from the table, duplicating the column under a new name, or replacing values. But that's not all. From this menu, you can also split text columns into multiples by common delimiters. The Power Query Editor ribbon contains additional tools that can help you to change the data type of columns, add scientific notation, or extract elements from dates, such as day of the week. No need to worry if you make a mistake. You can undo any step from the Applied Steps list. As you apply transformations, each step appears in the Applied Steps list on the Query Settings pane. You can use this list to undo or review specific changes, or even change the name of a step. To save your transformations, select Close and Apply on the Home tab. After you select Close and Apply, Power Query Editor applies the query changes and applies them to Power BI Desktop. Now that you know how to transform data, let's look at how to clean data. While Power BI can import your data from almost any source, you will get the best results from your visualization and modeling tools by using columnar data. In some cases, Excel spreadsheets will not format your data in simple columns. Let's have a look at how Power Query Editor, will help you to clean your columnar data. Now this table might look good to the human eye, but it is not ideal for automated queries. In this example, the spreadsheet contains headers that span across multiple columns. So how do you clean this up? You won't need to take out your cleaning cloth for this. All you need is Power Query Editor to clean your data. With this tool, you'll be able to quickly transform multi-column tables into data sets. Next, you'll learn about transposing data. Did you know that you can swap rows into columns to better format data? Well, with Power Query Editor, now you can, it's as simple as clicking the Transpose button. You might be wondering what other formatting can be done. Even though Power BI is quite intelligent, it might need your help to format data in a way that can be categorized and identified. Some transformations allow you to cleanse data into a data set that you can use in Power BI. Some powerful transformations include promoting rows into headers, using fill to replace null values, and unpivot columns. Let's look at some more useful information. Feel free to experiment with Power BI transformations, to determine which will transform your data into the most usable columnar format. If you come across a transformation that is not working the way you'd like it to, select the X next to the step and simply undo it. Just keep in mind that deleting a step may affect steps underneath. Once you've cleaned your data into a format that can be used, you can start by creating powerful visuals in Power BI. See you soon.
