Configuring table and column properties is a key element of data analysis in Power Platform. This concept remains highly relevant for those preparing for the PL-300 Microsoft Power BI Data Analyst Exam, where you’ll need an in-depth understanding of how to work with tables and columns within Power BI.
Working with Tables in Power BI:
In Power BI, tables are vital for arranging data in a structure that is easy to understand. Here’s how to configure a table in Power BI:
- Import the Data: In Power BI Desktop, go to Home > External Data > Get Data.
- Load your Table: Click on Load when the Navigator dialog box appears and select the table you want to import.
- Configure the Table: Select your table, then go to the Visualizations pane > Table.
Now your data will be displayed in a table. You can directly configure the table properties from here.
Configuring Column Properties in Power BI:
Column properties determine how data is displayed within a column. Here’s how you can configure column properties in Power BI:
- Select Your Column: Go to Fields pane, expand your table, and select the column that you want to configure.
- Configure Column Properties: Go to Column tools > Manage Columns. Here, you will have different options like renaming the column, changing data types, and more.
Some of the important column properties you should be aware of while preparing for the PL-300 Microsoft Power BI Data Analyst exam include:
- Data Type: Power BI supports various data types like whole number, decimal number, text, boolean, and date/time.
- Format: It allows you to change the format of the data. For example, if your column contains a date, you can choose how you want to display the date (dd-mm-yyyy vs mm-dd-yyyy).
- Sort By Column: With this property, you can select another column to sort the current column by its values.
- Summarization: It determines how Power BI aggregates column data. You can set it to Sum, Average, Min, Max, Count, or None.
Sample Code for Configuring Column Properties:
Although Power BI provides an intuitive graphical interface, you can also configure column properties using DAX. Here’s a simple example of how to change the data type of a column to ‘Text’:
Sales[Order ID] = FORMAT(Sales[Order ID], “General Number”)
This code will change the data type of ‘Order ID’ to Text. Note: ‘Sales’ is the table name here.
In conclusion, configuring tables and columns in Power BI enables you to make data more readable and more meaningful for your end-users, making this an essential skill for the PL-300 Microsoft Power BI Data Analyst exam. By understanding and practicing the steps and properties outlined above, you’ll be well-equipped to deal with this crucial aspect of Power BI. Aim to familiarize yourself with the building and altering of tables and columns in a variety of scenarios for comprehensive exam preparation.
Practice Test
True or False: You can change the column name in Power BI by double-clicking the column heading.
- True
- False
Answer: True
Explanation: In Power BI, you can rename a column by either double-clicking the column heading or by right-clicking the column heading and choosing the Rename option.
True or False: You can change the data type of a column in Power BI.
- True
- False
Answer: True
Explanation: Power BI allows users to change the data type of columns. You can do this by selecting Column tools > Data type in the Columns pane.
In Power BI, what are the two ways to hide a column?
- a) By renaming the column
- b) By selecting the column and choosing Hide from the menu
- c) By choosing the Hide option from the Column tools pane
- d) By changing the data type of the column
Answer: b) By selecting the column and choosing Hide from the menu, c) By choosing the Hide option from the Column tools pane
Explanation: In Power BI, you can hide a column by selecting the column and choosing Hide from the menu, or by choosing the Hide option from the Column tools pane.
What determines the sort order of the columns in Power BI?
- a) The order in which they were created
- b) The alphabetical order of their names
- c) The data type of the columns
- d) The order can be manually adjusted by the user
Answer: d) The order can be manually adjusted by the user
Explanation: By default, Power BI sorts columns based on their order in the data source, but users can manually adjust this order in the Columns pane.
True or False: Once a table is created in Power BI, its structure cannot be modified.
- True
- False
Answer: False
Explanation: The structure of a table in Power BI can be modified even after its creation. Users can add new columns, remove existing columns, change data types, and more.
True or False: You can copy a table structure in Power BI.
- True
- False
Answer: True
Explanation: Power BI allows copying of table structure using the “Duplicate table” feature. This copies the table structure, but no data is copied.
True or False: The name of a table in Power BI is case-sensitive.
- True
- False
Answer: True
Explanation: In Power BI, the name of a table is case-sensitive. For example, ‘Sales’ and ‘sales’ would be considered two different tables.
Which of the following are column tools in Power BI?
- a) Rename
- b) Transform
- c) Hide
- d) Delete
Answer: a) Rename, c) Hide, d) Delete
Explanation: Rename, Hide and Delete are all tools that can be used to manipulate columns in Power BI. Transform is not a column tool.
What is the maximum length of a column name in Power BI?
- a) 256 characters
- b) 128 characters
- c) 64 characters
- d) 32 characters
Answer: b) 128 characters
Explanation: In Power BI, the maximum length of a column name is 128 characters.
Which of the following is not a table property in Power BI?
- a) Table Name
- b) Data Source
- c) Column Order
- d) Table size
Answer: d) Table size
Explanation: Table size is not a direct property you can configure for a table in Power BI. The Table Name, Data Source, and Column Order are all configurable properties.
Interview Questions
What is the correct navigation path to configure table properties in Power BI?
The correct navigation path to configure table properties in Power BI is Modeling > Properties.
Which property allows you to update the name of a table in Power BI?
The “Name” property in the properties pane allows you to update the name of a table in Power BI.
How can you set the data type of a column in Power BI?
You can set the data type of a column by selecting the column, then from the “Columns tools” tab, select “Data type” and choose the desired type.
What is the use of the “Sort by column” feature in Power BI?
The “Sort by Column” feature in Power BI allows you to specify a column to order another column in visuals.
Can you modify the formatting of a single column in Power BI?
Yes, you can modify the formatting of a single column by selecting the column and from the “Column Tools” tab select “Format” and adjust properties like font color, font family, and background color.
What happens if the Summarize By property is set to “Don’t Summarize” to a numeric column in a table?
If the Summarize By property is set to “Don’t Summarize” for a numeric column, it will display individual values for each record instead of providing the sum, average, minimum, maximum, or count of the values in the column.
After renaming a table in Power BI, will the existing relationships with other tables be affected?
No, renaming a table will not affect its relationships with other tables in Power BI. The relationships are based on columns, not on the table names.
What is the “Synonyms” property used for in a Power BI table?
The “Synonyms” property in a Power BI table is used for enabling the Q&A feature to recognize the table with other possible names that users might use while querying.
What does the “Data category” property do in Power BI?
The “Data category” property in Power BI assigns a specific category to a column, which can affect how Power BI interprets and represents the data.
Is it possible to hide a column from report view in Power BI and how can this be achieved?
Yes, it is possible to hide a column from report view in Power BI. This can be achieved by selecting the particular column and turning on the “Hide in report view” option in the column tools properties.
What is the purpose of the “Description” property in Power BI table?
The “Description” property in Power BI table provides a way to document what the table is used for, details about its data, or other useful information that can be seen in Power BI Desktop or Power BI service while hovering over the table name.
What is the use of the “Default Summarization” property in Power BI?
The “Default Summarization” property in Power BI defines how values for this column should be summarized when added to a visualization, such as sum, average, min, max, or count.
How do we set “Default Summarization” for a column in Power BI?
To set “Default Summarization” for a column, select the column, then from the “Column Tools” tab, select “Default Summarization” and choose from the listed options.
Is it possible to change the data type of a column in a loaded table in Power BI?
Yes, it is possible to change the data type of a column in a loaded table by selecting the column, going to the “Column Tools” tab, and then selecting the desired data type from the “Data type” dropdown list.
Which property defines the visual action when a user clicks a field value in Power BI?
The “Action” property defines the visual action when a user clicks a field value in Power BI. It can be set to either Drill Through, Url, or None.
extutorials.com