Splitting and merging columns in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate the number of unique customers after splitting a full name column

You have a table with a column FullName containing first and last names separated by a space (e.g., "John Smith"). You split this column into FirstName and LastName. Then you want to count unique customers by LastName.

Which DAX measure correctly counts unique last names?

Power BI

UniqueLastNames = DISTINCTCOUNT('Customers'[LastName])

A UniqueLastNames = COUNT('Customers'[LastName])

B UniqueLastNames = COUNTROWS(VALUES('Customers'[FullName]))

C UniqueLastNames = DISTINCTCOUNT('Customers'[LastName])

D UniqueLastNames = DISTINCTCOUNT('Customers'[FirstName])

💡 Hint

Think about which column contains the last names after splitting.

After splitting, the LastName column holds last names. Using DISTINCTCOUNT on it counts unique last names correctly. Option C counts unique full names, not last names. Option C counts all rows, not distinct. Option C counts unique first names, not last names.

❓ visualization

intermediate

Best visualization to show merged city and state data

You merged two columns City and State into one column Location (e.g., "Seattle, WA"). You want to show sales by location on a report.

Which visualization type is best to clearly show sales by each location?

A A line chart showing sales trend over time by Location

B A pie chart showing sales percentage by Location

C A scatter plot with Location on X axis and sales on Y axis

D A bar chart with Location on the axis and sales as values

💡 Hint

Think about which chart clearly compares sales amounts by categories.

A bar chart is best to compare sales amounts by each location category. Pie charts are less clear with many categories. Line charts show trends over time, which is not the goal here. Scatter plots are for numeric relationships, not categories.

❓ data_modeling

advanced

Handling merged columns for filtering in Power BI

You merged ProductCategory and ProductSubcategory into a single column CategoryFull with format "Category - Subcategory". You want to enable filtering by either category or subcategory in reports.

What is the best data modeling approach to support filtering by both parts?

A Keep only the merged column and use text filters on it

B Keep original separate columns and create the merged column as a calculated column

C Remove original columns and split the merged column back when filtering

D Create a disconnected table with all unique merged values for filtering

💡 Hint

Think about how to keep flexibility for filtering on both category and subcategory.

Keeping original columns and creating a merged calculated column allows filtering by category or subcategory independently, while also showing the merged value. Option B loses filtering flexibility. Option B is inefficient and error-prone. Option B complicates filtering without clear benefit.

🔧 Formula Fix

advanced

Identify the error in this DAX formula for splitting a column

You want to extract the first word from a FullName column using this DAX formula:

FirstName = LEFT('Table'[FullName], FIND(" ", 'Table'[FullName]))

What error will this formula cause?

A It returns an error when there is no space in FullName

B It causes a syntax error due to missing comma

C It returns the first name with an extra space at the end

D It returns the full name without splitting

💡 Hint

Consider what happens if FIND does not find a space.

If FullName has no space, FIND(" ", 'Table'[FullName]) returns an error, causing the whole formula to error. Option A is incorrect because the formula includes the space. Option A is wrong; syntax is correct. Option A is wrong; it does not return full name.

🧠 Conceptual

expert

Best practice for merging columns with different data types

You want to merge a Date column and a Text column into one combined column in Power BI. What is the best practice to avoid errors and maintain data quality?

A Convert the Date column to text format before merging

B Merge columns directly without conversion; Power BI handles types automatically

C Convert the Text column to date format before merging

D Create a new numeric column representing date as a number and merge with text

💡 Hint

Think about how Power BI treats different data types when merging columns.

To merge columns with different data types, convert the Date column to text first. This avoids errors and ensures the merged column is consistent text. Option A can cause errors or unexpected results. Option A is invalid if text is not a date. Option A complicates the model unnecessarily.

Practice

1. What does splitting a column in Power BI do?
Splitting means:

easy

A. Breaking one column into multiple columns based on a rule

B. Joining multiple columns into one column

C. Deleting unwanted columns from the table

D. Changing the data type of a column

Solution

1. Step 1: Understand the meaning of splitting

   Splitting means dividing one column into several columns based on a delimiter or rule.

2. Step 2: Compare with other options

   Joining columns is merging, deleting is removing, and changing data type is different from splitting.

3. Final Answer:

   Breaking one column into multiple columns based on a rule -> Option A

4. Quick Check:

   Splitting = Breaking column into parts [OK]

Hint: Splitting breaks one column into parts [OK]

Common Mistakes:

• Confusing splitting with merging
• Thinking splitting deletes columns
• Mixing splitting with data type changes

2. Which of the following is the correct way to merge two columns FirstName and LastName with a space separator in Power Query?

easy

A. Table.RemoveColumns(Source, {"FirstName", "LastName"})

B. Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" "))

C. Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName])

D. Table.TransformColumnTypes(Source, {"FullName", type text})

Solution

1. Step 1: Identify merging syntax in Power Query

   Merging columns uses Table.AddColumn with an expression combining columns using & and a separator.

2. Step 2: Check each option

   Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName]) correctly concatenates FirstName and LastName with a space. Table.RemoveColumns removes columns, Table.SplitColumn splits columns, Table.TransformColumnTypes changes data types.

3. Final Answer:

   Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName]) -> Option C

4. Quick Check:

   Merge = AddColumn with & [OK]

Hint: Use & with space inside AddColumn to merge columns [OK]

Common Mistakes:

• Using SplitColumn instead of AddColumn for merging
• Forgetting the space separator
• Removing columns instead of merging

3. Given a column FullName with values like "John Doe", what will be the result of this Power Query step?

Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" "), {"FirstName", "LastName"})

medium

A. Two new columns named FirstName and LastName with split parts

B. A single column with names joined by space

C. An error because SplitColumn needs a number of columns

D. The FullName column deleted from the table

Solution

1. Step 1: Understand SplitColumn with delimiter

   This function splits the FullName column by space into two columns named FirstName and LastName.

2. Step 2: Check other options

   A single column with names joined by space is merging, not splitting. An error because SplitColumn needs a number of columns is incorrect because specifying new column names is valid. The FullName column deleted from the table is false; the original column is replaced by new columns.

3. Final Answer:

   Two new columns named FirstName and LastName with split parts -> Option A

4. Quick Check:

   SplitColumn with delimiter creates multiple columns [OK]

Hint: SplitColumn with delimiter creates named new columns [OK]

Common Mistakes:

• Thinking SplitColumn merges columns
• Assuming original column remains unchanged
• Believing it causes an error without column count

4. You tried to merge columns City and Country using this code:

Table.AddColumn(Source, "Location", each [City] + ", " + [Country])

But you get an error. What is the likely cause?

medium

A. Missing a space after the comma separator

B. Using + operator instead of & for text concatenation

C. Table.AddColumn cannot merge columns

D. Column names are case sensitive and incorrect

Solution

1. Step 1: Check concatenation operator in Power Query

   Power Query uses & to join text, not + which causes an error.

2. Step 2: Review other options

   Missing space after comma is not an error, AddColumn can merge columns, and case sensitivity depends on source but usually not error here.

3. Final Answer:

   Using + operator instead of & for text concatenation -> Option B

4. Quick Check:

   Use & for text join, not + [OK]

Hint: Use & to join text columns, not + [OK]

Common Mistakes:

• Using + instead of & for text
• Assuming AddColumn can't merge
• Ignoring case sensitivity issues

5. You have a column Address with values like "123 Main St, Springfield, IL". You want to split it into Street, City, and State columns. Which Power Query step correctly splits this?

hard

A. Table.TransformColumns(Source, {"Address", each Text.Split(_, ", ")})

B. Table.AddColumn(Source, "Street", each Text.BeforeDelimiter([Address], ", "))

C. Table.SplitColumn(Source, "Address", Splitter.SplitTextByEachDelimiter({","}), {"Street", "City", "State"})

D. Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(", "), {"Street", "City", "State"})

Solution

1. Step 1: Identify correct delimiter and split method

   The address uses ", " as delimiter, so SplitTextByDelimiter with ", " is correct to split into three columns.

2. Step 2: Evaluate other options

   Table.AddColumn(Source, "Street", each Text.BeforeDelimiter([Address], ", ")) only extracts Street, not full split. Table.SplitColumn(Source, "Address", Splitter.SplitTextByEachDelimiter({","}), {"Street", "City", "State"}) uses SplitTextByEachDelimiter with wrong delimiter format. Table.TransformColumns(Source, {"Address", each Text.Split(_, ", ")}) splits but does not create separate columns.

3. Final Answer:

   Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(", "), {"Street", "City", "State"}) -> Option D

4. Quick Check:

   SplitColumn with correct delimiter and column names [OK]

Hint: Use SplitTextByDelimiter with exact separator and column names [OK]

Common Mistakes:

• Using wrong delimiter like comma without space
• Extracting only one part instead of splitting all
• Not naming new columns after split