SHAPING DATA WITH POWER QUERY EDITOR

The query ribbons

The ribbon in Power Query Editor consists of four tabs: Home, Transform, Add Column, View, Tools, and Help.

The Home tab contains the common query tasks.

The Transform tab provides access to common data transformation tasks, such as:

·         Adding or removing columns

·         Changing data types

·         Splitting columns

·         Other data-driven tasks

Shape data

Now that you’re connected to a data source, you can adjust the data to meet your needs. To shape data, you provide Power Query Editor with step-by-step instructions for adjusting the data while loading and presenting it. Shaping doesn’t affect the original data source, only this particular view of the data.

Shaping can mean transforming the data, such as renaming columns or tables, removing rows or columns, or changing data types. Power Query Editor captures these steps sequentially under Applied Steps in the Query Settings pane. Each time this query connects to the data source, those steps are carried out, so the data is always shaped the way you specify. This process occurs when you use the query in Power BI Desktop, or when anyone uses your shared query, such as in the Power BI service.

First step is to identify the column header and names within the data and then evaluate where they are located to ensure they are in a right place.

How to correct Header when they are not place in the right place.

1.    By select the use first Row as Headers on a Home tab or

2.    By selecting the dropdown button next to the column 1 and then select use first Row as Header.

Next step is to rename column. You can Re-name column when you see that after selecting first Row as Header and some names does not appear rightly, you can click the column to rename it with the right name.

Remove Top Rows

When shaping your data, you might need to remove some of the top rows, if they are blank or if they contain data that you do not need in your project.

To remove the excess rows, select remove rows on Home tab and click remove top rows.

(You can specify by remove top 10 rows). Same applicable to bottom rows.

Remove Columns

A key step of data shaping process is to remove unnecessary columns. Examine each column and ask yourself if you really need the data that it contains.

How to remove column

1.    Select the column you want to remove and on Home tab, select remove column or

2.    You can select the columns you need and then on the Home tab select remove column and click remove other columns.

Change data type

If you need to change a data type, select the column or columns to change. right-click a column header, select Change Type, and choose a new data type from the menu, or drop down the list next to Data Type in the Transform group of the Home tab, and select a new data type.

Split Column by Delimiter

When you have many items in a column (ie Full Name) and you want to split the name to be FirstName on its separate column from LastName.

Right click the column and select split column and choose by delimiter then select space if it is space that separate FirstName from LastName and click Ok to separate them to two columns then Rename your column.

Views

Column Quality; it is used to show information of the column (ie Valid, error and empty).

Column profile; giving information about data but its limit to one column.

Column distribution; shows the number of distinct and unique values.