Class Six: Text (Data Cleaning using Power Query Editor)

Power Query Editor has several functions for data analysis, including:

Importing Data from Multiple Sources: Power Query Editor supports importing data from various sources such as databases, files, web pages, and even other Microsoft applications.

Transforming and Cleaning Data: The “Transform” feature in Power Query Editor enables users to filter, sort, and group data.

 Steps to transform your data using Power Query Editor in Excel:

Open Power Query Editor: To open Power Query Editor, go to the “Data” tab in the main menu, then click “Get Data” in the “Get & Transform Data” group. In the window that opens, select your data source (e.g., “From Excel workbook” if the data is in an Excel sheet).

Load Data into Query Editor: Select your data source and click “Load” to load the data into the Query Editor window.

Change data type:

– Select the column you want to change the data type for.

– Go to “Transform” tab, click “Data Type” and select the desired data type (e.g., Date, Whole Number, Decimal Number).

– Alternatively, right-click on the column > “Change Type” > select the desired data type.

 Replace header with appropriate header:

 Go to “Transform” tab, click use first row as a header to select appropriate header from the rows.

 Remove unnecessary columns:

– Select the column(s) you want to remove.

– Right-click on the column header > “Remove”            

– Alternatively, go to “Home” tab and click  “Remove Columns”.