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_1Case 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_1Step-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-fl...
|
|
