Create and transform columns

Creating and transforming columns in Power BI is an essential operation for data analysts aiming for the PL-300 Microsoft Power BI Data Analyst certification exam. This skill is important in refining datasets to suit specific analysis requirements and generate valuable insights.

Creating Columns in Power BI

Creating a column in Power BI involves either adding a new column to an existing table or creating a calculated column. A new column can store additional data input directly, while a calculated column carries out certain operations or calculations on existing datasets.

Here’s an example of creating a calculated column:

Assume you have a ‘Sales’ table with ‘Item_Cost’ and ‘Quantity’ columns, and you want to create a ‘Total_Cost’ calculated column. In Power BI, you would do it as follows:

1. Navigate to the Data view.
2. Select ‘Sales’ table.
3. On the modeling tab, click on ‘New Column’.
4. In the formula bar, write the expression, `Total_Cost = Sales[Item_Cost] * Sales[Quantity]`.
5. Press Enter.

By using these steps, a new calculated column ‘Total_Cost’ will be added in the ‘Sales’ table, the value for each row in the ‘Total_Cost’ column is the product of the corresponding ‘Item_Cost’ and ‘Quantity’.

Transforming Columns in Power BI

Transforming columns involves changing the data type, formatting the data, and performing operations that alter the shape or layout of the data. One common type of transformation is changing the data type of the column.

For instance, if you have a column ‘Date’ in string format (e.g., “20211003”), you might want to transform this text format into a date format (e.g., 2021-10-03). In Power BI, you can achieve this using Power Query Editor:

1. Open Power Query Editor.
2. Select the ‘Date’ column.
3. Go to the ‘Transform’ tab.
4. In the ‘Data Type’ dropdown, select ‘Date’.
5. Apply changes and close the Editor.

This will transform your ‘Date’ column from text format to date format, which can be understood and processed better by the reporting tools in Power BI.

Another significant operation under column transformation is pivoting and unpivoting columns. This function changes the structure of your data to suit specific analysis needs.

Pivoting the data converts unique values from one column into multiple columns in the output and performs aggregations where required on any remaining column values. Whereas, unpivoting would turn multiple columns into a single column.

Remember, while creating and transforming columns, the main goal is to improve data quality, enhance data readability, and structure data to suit specific analysis needs. By mastering these column operations, you’ll improve your Power BI data modeling skill, a critical component of the PL-300 exam.

Practice Test

True/False: Power BI allows you to create and transform columns via Power Query or directly in Data View.

• True
• False

Answer: True

Explanation: Power BI provides flexibility in how you manipulate your data. You can manage your columns either directly in Data View or using Power Query.

In Power BI, can you use mathematical formulas to create calculated columns?

• Yes
• No

Answer: Yes

Explanation: Calculated columns in Power BI can involve mathematical formulas, string manipulation, and logical comparisons.

Which of these options are valid methods to transform columns in Power BI?

• a) Power Query
• b) DAX
• c) Python
• d) Data View

Answer: a) Power Query, b) DAX, d) Data View

Explanation: Power Query, DAX, and data view functionalities can be utilized to transform columns in Power BI. Python is not a valid method for column transformation.

True or False: A calculated column in Power BI can be based on values from different tables.

• True
• False

Answer: False

Explanation: Calculated columns operate at the row level and can only reference columns from the same table.

True/False: It’s possible to create a new column based on existing columns in Power BI.

• True
• False

Answer: True

Explanation: You can create a new column in Power BI based on existing columns using Calculated Columns.

Which function do you use to create a calculated column using multiple conditions in Power BI?

Answer: SWITCH function

Explanation: The SWITCH function in Power BI can be used to create calculated columns based on multiple conditions.

What happens if you rename a column in Power BI that is being used in a calculation elsewhere?

Answer: The calculation updates automatically.

Explanation: Power BI will automatically update any calculations that reference a renamed column, maintaining the validity of the calculation.

What can you NOT do with Power Query in Power BI?

• a) Change the data type of a column.
• b) Rename a column.
• c) Add a new column calculated from existing columns.
• d) Delete a column.
• e) Create visualization.

Answer: e) Create visualization.

Explanation: Power Query is used for data transformation and cleaning in Power BI. It does not have the capabilities for creating visualizations.

True/False: The calculated columns consume memory in Power BI’s data model.

• True
• False

Answer: True

Explanation: Calculated columns consume memory in the data model within Power BI as they store the result of the calculations for each row in the table.

In Power BI, what function would you use to unify the format of a string column (for example, change to upper case)?

Answer: UPPER function

Explanation: The UPPER function in Power BI is used to convert all the letters in a text string to uppercase.

You want to create a new date column based on an existing text column in Power BI. Which function should you use?

Answer: DATEVALUE function

Explanation: The DATEVALUE function in Power BI converts a text string representation of a date into a date/time data type.

True/False: DAX is used to create new columns and tables in Power BI.

• True
• False

Answer: True

Explanation: Data Analysis Expressions (DAX) is a formula language in Power BI that allows users to create custom columns and tables among other operations.

Which of these is NOT a transformation option in Power Query in Power BI?

• a) Split column
• b) Merge columns
• c) Extract values from a column
• d) Randomize column order

Answer: d) Randomize column order

Explanation: Power Query in Power BI provides numerous transformation options, but randomizing the column order is not one of them.

True/False: In Power BI, after creating a calculated column, you can update the values in it manually.

• True
• False

Answer: False

Explanation: In Power BI, a calculated column contains values calculated from an expression or formula. It cannot be updated manually.

Which of these options would you NOT use to replace NULL or blank values in a column in Power BI?

• a) Replace Values function
• b) Coalesce function
• c) Fill function
• d) Calculate function

Answer: d) Calculate function

Explanation: In Power BI, Replace Values, Coalesce and Fill functions can be used to replace NULL or blank values. The Calculate function is used for completely different purposes.

Interview Questions

How can you create a new column in Microsoft Power BI?

In the Power BI Desktop, select the “Modeling” tab and click on “New Column”. You can then input the code for your custom column in the formula bar.

What is the DAX function used to calculate columns in Power BI?

The Data Analysis Expressions (DAX) language is used for creating custom formulas in Power BI.

Can existing fields be transformed in Power BI?

Yes, Power BI allows users to transform existing fields using the “Transform” tab in the Power Query Editor.

How can you transform a text column into a date column in Power BI?

In the Power Query Editor, click on the column you want to transform, go to “Transform” tab and select “Date” > “Date”.

What types of data transformation can be done in Power BI?

Power BI supports many types of data transformation including appending, merging, grouping, renaming, and changing the data type of existing columns.

What is the role of the ‘Split Columns’ transformation in Power BI?

Split Columns transformation in Power BI is useful to divide a single column into multiple columns.

What is a Calculated Column in Power BI?

A Calculated Column is a column that you add to an existing table in the Power BI Desktop and create using a DAX formula that uses data already in the table.

How do you delete a column in Power BI?

In the Preview pane in Power Query Editor, select the column you want to remove. Then, on the Home tab, in the Manage Columns group, select Remove Columns.

How can you change a column data type in Power BI?

Select the column in Power Query Editor, then on the “Transform” tab, in the “Any Column” group, select “Data Type” then choose the new data type.

What is the difference between a Calculated Column and a Measure in Power BI?

A Calculated Column operates at the row level and adds new data to a table, while a Measure operates at a higher level, working on the columns summarizing data with an aggregate function.

How do you rename a column in Power BI?

In Power Query Editor, select the column header of the column you want to rename, and on the Home tab, in the Transform group, select Rename.

How do you hide a column in Power BI?

In the Fields pane, right-click the field you want to hide, and then select “Hide”.

How can you duplicate a column in Power BI?

In Power Query Editor, select the column to duplicate and then go to “Add Column” > “Duplicate”.

What is the primary purpose of the ‘Group By’ transformation in Power BI?

The primary purpose of the ‘Group By’ transformation in Power BI is to aggregate data according to certain columns.

Is it possible to transform a number column into a text column in Power BI?

Yes, to transform a number into a text column, select the column, go to “Transform” tab, and select “Text”.