By -Ashish Kumar
In this guide, we explore how to create a custom column in Power BI that dynamically identifies the attribute name based on non-null values across multiple columns. This process enhances data analysis flexibility by allowing you to effectively manage datasets where different columns hold values for different categories, such as sales data across various product types. The approach leverages Power Query to handle two distinct scenarios:
Case 1: Where only one of the value columns will contain data, and the rest remain null.
Case 2: Where multiple value columns may contain data, and we need to identify the first non-null value starting from a specific column.
By following the step-by-step guide provided, you will learn how to implement a dynamic solution that adapts to both cases, helping you streamline data processing and analysis in Power BI. The use of Power Query allows for easy customization, making this technique versatile for various reporting needs. Whether you're new to Power BI or an experienced user, this method will significantly improve your ability to handle complex data scenarios efficiently.
Category: This represents the product type (e.g., Apple, Mango).
Value_1, Value_2, Value_3: These represent sales values for different categories, where each row can have one or more filled with values.
(I) Case 1: - It is fixed that only one column will hold a value and other columns value will remain null just like below table.
Category | Value_1 | Value_2 | Value_3 | Attribute Name |
Apple | 5 |
|
| Value_1 |
Apple |
| 6 |
| Value_2 |
Apple |
|
| 8 | Value_3 |
Mango |
| 10 |
| Value_2 |
Mango |
|
| 15 | Value_3 |
Mango | 23 |
|
| Value_1 |
(II) Case 2: - All columns except for category can have values and we need to show which is the first non-null value in all of those (Table scan starts from Value_1 column).
Category | Value_1 | Value_2 | Value_3 | Attribute Name |
Apple | 11 | 2 | 6 | Value_1 |
Apple |
| 6 | 9 | Value_2 |
Apple |
|
| 8 | Value_3 |
Mango |
| 10 | 1 | Value_2 |
Mango |
|
| 15 | Value_3 |
Mango | 23 | 21 | 13 | Value_1 |
Below is the Step-by-Step guide which can help us in creating the Attribute Name Column.
Step 1: Open the Power BI Desktop App, and go to the Power Query window by clicking on ‘Transform Data’
Step 2: Once you're in the Power Query Editor, select the tables on which you are going to work. (In our scenario, we have two tables for two different cases). CASE 1 Table
CASE 2 Table
Step 3: Click on Add Column from the ribbon and select a custom column option in the Power Query Window.
Step-4: Copy the code below and paste it in both tables Case-1 and Case-2, as we would be testing one single code to counter both of the cases. Copy Code: - let
// Get the list of all column names in the table
columnNames = Table.ColumnNames(#"Previous Step"),
// Filter the column names to include only those that start with "Value" valueColumns = List.Select(columnNames, each Text.StartsWith(_, "Value_")), // Function to find the first non-null value's column name firstNonNullColumn = List.First(List.Select(valueColumns, (col) => Record.Field(_, col) <> null), null)
in firstNonNullColumn
NOTE: In the above code, instead of "Previous Step", please mention your respective previous step name.
Step-4: After creating the same custom columns for both tables, it would look like the snapshot below: Case –1 Table
Case –2 Table
That’s it! With just a few simple steps, you’ve unlocked the power of dynamic attribute identification in Power BI. Give it a go at your own data and watch your reports transform. If you’ve got questions, challenges, or even tips of your own, I’d love to hear from you in the comments stay tuned for more exciting tips and tricks to level up your Power BI game! Until next time, happy reporting!
Comments