Power BI: Convert Single Column to Multiple Columns with Nulls

To convert a single column containing series names into multiple columns with null values in Power BI, you’ll need to use either the Unpivot or Pivot transformation in Power Query, depending on your current data format. If you have data in a long format with ‘Series Name’ as a column containing ‘List’, ‘All’, ‘Ordered’, and ‘Rejected’, you’ll need to first unpivot to create an attribute-value pair structure, then pivot to transform these into separate columns while preserving null values.

Contents

• Understanding Your Data Structure
• Method 1: Using Unpivot then Pivot Transformation
• Method 2: Custom M Code Solution
• Handling Null Values During Transformation
• Troubleshooting Common Issues
• Best Practices for Data Transformation

Understanding Your Data Structure

Before performing the transformation, it’s essential to understand your current data format. Based on your description, you likely have data in a “long” format where each row represents a single data point with columns like:

• Hire (category)
• Series Name (containing ‘List’, ‘All’, ‘Ordered’, ‘Rejected’)
• Value (the actual data point)

The goal is to transform this into a “wide” format where each series becomes its own column, creating null values where data doesn’t exist for specific combinations of Hire and Series categories.

“Unpivoting refers to a transformation where you move columns into rows, but Power Query by default removes any cells with a null value.” - Rick de Groot on LinkedIn

Method 1: Using Unpivot then Pivot Transformation

This approach is most straightforward when your data is already in a format with separate columns for each series that you need to unpivot first.

Step 1: Prepare Your Data

1. Open Power Query Editor in Power BI
2. Select your query containing the series data

Step 2: Unpivot the Series Columns

If your data has separate columns for each series that you need to unpivot:

1. Select all the series columns (List, All, Ordered, Rejected)
2. Right-click and select “Unpivot Other Columns” or go to Transform tab → Unpivot → Unpivot Other Columns
3. This creates two new columns: “Attribute” (series name) and “Value” (data points)

“It’s simple: just provide the columns you want to move, and the unpivot function does the rest. But by default, it removes any cells with a null value.” - BI Gorilla

Step 3: Handle Null Values Before Pivoting

Since unpivot removes null values by default, you need to preserve them:

1. Before unpivoting, select the series columns
2. Go to Transform tab → Replace Values
3. Replace null values with a placeholder (like “0” or blank space)
4. Perform the unpivot operation
5. After unpivoting, you can replace the placeholder back to null if needed

“Try replacing the null value with a blank space in the four questions columns that you are unpivoting.” - Microsoft Fabric Community

Step 4: Pivot Back to Wide Format

1. Select the “Attribute” column
2. Go to Transform tab → Pivot Column
3. Set “Values Column” to your value column
4. This creates separate columns for each series name

Method 2: Custom M Code Solution

For more complex scenarios or when you need precise control over the null value handling, you can use custom M code.

Basic M Code Structure

let
    Source = YourDataSource,
    // Replace nulls with placeholder before transformation
    ReplaceNulls = Table.ReplaceValue(Source, null, "NULL_PLACEHOLDER", Replacer.ReplaceValue, {"List", "Ordered", "Rejected"}),
    // Unpivot the columns
    Unpivoted = Table.UnpivotOtherColumns(ReplaceNulls, {"Hire"}, "Attribute", "Value"),
    // Pivot back to wide format
    Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value"),
    // Replace placeholder back to null
    FinalResult = Table.TransformColumns(Pivoted, {{"List", each if _ = "NULL_PLACEHOLDER" then null else _}, 
                                                   {"Ordered", each if _ = "NULL_PLACEHOLDER" then null else _}, 
                                                   {"Rejected", each if _ = "NULL_PLACEHOLDER" then null else _}})
in
    FinalResult

Advanced M Code for Dynamic Series Handling

If you need to handle dynamic series names or more complex scenarios:

let
    Source = YourDataSource,
    // Get all unique series names
    SeriesNames = Table.ColumnNames(Source),
    // Filter out non-series columns (like Hire)
    SeriesColumns = List.Select(SeriesNames, each not List.Contains({"Hire"}, _)),
    // Replace nulls with placeholder
    ReplaceNulls = Table.ReplaceValue(Source, null, "NULL_PLACEHOLDER", Replacer.ReplaceValue, SeriesColumns),
    // Unpivot dynamically
    Unpivoted = Table.UnpivotOtherColumns(ReplaceNulls, {"Hire"}, "Series", "Value"),
    // Pivot back
    Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Series]), "Series", "Value"),
    // Clean up placeholders
    FinalResult = Table.TransformColumns(Pivoted, 
        List.Transform(SeriesColumns, each {_, each if _ = "NULL_PLACEHOLDER" then null else _}))
in
    FinalResult

Handling Null Values During Transformation

Preserving null values is crucial for maintaining data integrity. Here are several approaches:

Method A: Replace Before Transform

1. Select the columns you plan to transform
2. Go to Transform tab → Replace Values
3. Replace null with a temporary value like "NULL" or 0
4. Perform your unpivot/pivot operations
5. Replace the temporary value back to null

Method B: Use Custom Function

(SeriesColumn as text) as any =>
    if Source[SeriesColumn] = null then 
        null 
    else 
        Source[SeriesColumn]

Method C: Filter and Preserve

1. Create a backup of your original data
2. Work with a filtered version for transformations
3. Merge results back with original to preserve nulls

“Problem: Empty cells create null values after unpivot. Solution: Use Transform → Replace Values to replace null with 0 or remove null rows” - UnpivotTool

Troubleshooting Common Issues

Issue 1: Missing Rows After Unpivot

Problem: Some rows disappear after unpivoting due to null values
Solution: Replace nulls with placeholders before transformation, as mentioned earlier

"In the example below, notice that Andrew has no sales and is excluded after the unpivoting… Select Monday to Friday in Query Editor, Go to Transform Tab, Replace Values → Replace “null” with “0"” - Microsoft Fabric Community

Issue 2: Data Type Inconsistencies

Problem: Mixed data types after transformation
Solution: Use Table.TransformColumnTypes to standardize data types

Issue 3: Performance Problems with Large Datasets

Problem: Transformations run slowly with millions of rows
Solution:

• Filter data before transformation
• Use more efficient M code
• Consider staging tables

Issue 4: Dynamic Column Names

Problem: Series names might change over time
Solution: Use dynamic M code that automatically detects column names, as shown in the advanced M code example above

Best Practices for Data Transformation

1. Always Backup Your Data: Create a copy of your query before performing complex transformations

2. Use Placeholder Values: Replace nulls with temporary placeholders during transformation, then restore them

3. Test with Sample Data: Use a subset of your data to test transformations before applying to full dataset

4. Document Your Steps: Add comments to your M code explaining the transformation logic

5. Handle Data Types: Ensure consistent data types throughout the transformation process

6. Consider Performance: For large datasets, optimize your transformations by filtering early

7. Use Error Handling: Implement try-catch blocks for error-prone transformations

8. Validate Results: Always verify that the transformed data maintains the expected relationships and null value placements

“Unpivoting in the Power Query Editor… It essentially does the opposite of pivoting: instead of turning row values into column headers, it transforms column headers into row values.” - AlmaBetter

Conclusion

Transforming a single column with series names into multiple columns with null values in Power BI requires careful handling of null values during the transformation process. The key steps involve:

1. Understanding whether you need to unpivot first (if data is in wide format) or pivot directly (if in long format)
2. Using placeholder values to preserve null information during transformation
3. Applying either the built-in Power Query transformations or custom M code
4. Validating the results to ensure data integrity is maintained

By following the methods outlined above, you can successfully transform your ‘Series Name’ column into separate ‘List’, ‘All’, ‘Ordered’, and ‘Rejected’ columns while preserving the appropriate null values between rows where data doesn’t exist for all series categories.

Sources

1. BI Gorilla - Unpivot Columns And Keep Null Values in Power Query
2. Microsoft Fabric Community - Unpivot other columns with null values
3. Microsoft Fabric Community - Unpivot removes rows with no/null values
4. UnpivotTool - Power BI Unpivot Columns Guide
5. Rick de Groot on LinkedIn - Unpivot Columns And Keep Null Values
6. Enterprise DNA Forum - Unpivoting removes rows with null values
7. Microsoft Power BI Community - Unpivot removes rows with no/null values
8. AlmaBetter - Pivot and Unpivot in Power BI
9. Microsoft Fabric Community - All data’s do not appear on unpivoting the columns
10. RADACAD - Pivot and Unpivot with Power BI