Dynamic Attribute Identification in Power BI

Creating a Dynamic Attribute Name Column in Power BI Using Power Query

In this guide, we demonstrate how to create a custom column in Power BI that dynamically identifies the attribute name based on non-null values across multiple columns. This technique is useful for datasets where different columns hold values for different categories (e.g., sales data for various product types). We’ll cover two distinct scenarios using Power Query:

Case 1: Only one of the value columns contains data, and the rest are null.
Case 2: Multiple value columns may contain data, and we need to identify the first non-null value starting from a specific column.

Step-by-Step Guide

Category and Values Example:

• Category: Product type (e.g., Apple, Mango)
• Value_1, Value_2, Value_3: Sales values for different categories, where each row can have one or more filled values.

  Case 1: One column contains data, the others are null: 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 Case 2: Multiple columns may contain data, and we need the first non-null value: 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 Step-by-Step Instructions: Open Power BI Desktop: Go to the Power Query Editor by clicking on Transform Data. Select Tables: Choose the relevant tables for each case. Add Custom Column: Click Add Column > Custom Column in the Power Query window. Copy and Paste the Code: Use the following code for both tables (Case 1 and Case 2):   let // Get the list of all column names 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: Replace "Previous Step" with the appropriate step name. Outcome: After applying this custom column, the results for each table will display the respective attribute name based on the first non-null value. If you have any questions, please leave them in the comments below. Stay tuned for more Power BI tricks and tips! Explore more insights and solutions on our website: https://www.rayminds.com/post/dynamic-attribute-identification-in-power-bi-boosting-data-analysis-flexibility