Shaping Data in Power BI Desktop

In Power BI Desktop, there are options for you to connect to various different types of data sources. Then you may shape the data to meet your needs, enabling you to create visual reports to share with others. Shaping data refers to transforming the data including renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and more. Further, the process of Combining data refers to connecting two or more data sources, and shaping them as per requirement, then consolidating them into a helpful query.

Overview of Shaping Data

In Power Query Editor, when you shape data, you are required to provide step-by-step instructions for Query Editor to carry out for you to adjust the data as it loads and presents it. Further, the original data source is not affected and only a particular view of the data is adjusted/shaped.

Steps to shape data –

• Firstly, from the Add Column ribbon, select Custom Column, which lets you add a custom column.
• Secondly, in the Custom Column window, in New column name, enter New Rank (suppose).
• Thirdly, make sure the status message is No syntax errors have been detected, and select OK.
• Then, to keep column data consistent, transform the new column values to whole numbers.
• Further, to change them, right-click the column header, and then select Change Type > Whole Number.
• Now, if you need to choose more than one column, select a column, hold down SHIFT, select additional adjacent columns, and then right-click a column header. Also, you can use the CTRL key to choose non-adjacent columns.
• Then, to transform column data types, in which you transform the current data type to another, select Data Type Text from the Transform ribbon.
• Lastly, in Query Settings, the Applied Steps list reflects any shaping steps applied to the data. To remove a step from the shaping process, select the X to the left of the step.

Steps to Adjust Data

• Firstly, lets us suppose to remove the column, select the column, choose the Home tab from the ribbon, and then select Remove Columns.
• Secondly, right-click a step. Query Editor provides a menu that lets you rename, delete, delete until end, move up and move down.
• Thirdly, move up the last step, Removed Columns, to just above the Added Custom step.
• Then, Select the Added Custom step.
• Since we don’t need to display information about the errors, select Cancel.
• Further, to fix the errors, select the New Rank column, then display the column’s data formula by selecting the Formula Bar checkbox from the View tab.
• Now, to Remove the Cost of living parameter and decrement the divisor, by changing the formula
• Lastly, select the green checkmark to the left of the formula box or press Enter.