Forum Discussion
Merging columns
Hello,
I've a three columns table as follows in Power BI:
| Item | Value 1 | Value 2 |
| A | String 1 | |
| B | String 1 | String 4 |
| C | String 2 | |
| D | String 3 | String 5 |
| E | String 1 |
At the end I would like to have a table as follows:
| Item | Final Value |
| A | String 1 |
| B | String 1 |
| C | String 2 |
| D | String 3 |
| E | String 1 |
| B | String 4 |
| D | String 5 |
Does anyone knows how can I achieve this ? I've already tried a left outer join but I always end having the two columns (Value 1 & Value 2) and I would like to only have one at the the end...
output :
try this calculated table :
Table 2 =var ds_1 =filter(SUMMARIZE(items,items[Item],items[Value 1]),items[Value 1] <> blank())var ds_2 =filter(SUMMARIZE(items,items[Item],items[Value 2]),items[Value 2] <> blank())
RETURNUNION(ds_1,ds_2)let me know if this helps.
3 Replies
To achieve the desired table transformation in Power BI, you can use the "Unpivot Columns" feature in Power Query. Here are the steps to transform your table:
1. **Load Data into Power Query:**
- Open Power BI Desktop.
- Load your data into Power BI.
- Go to the "Home" tab and click on "Transform Data" to open Power Query Editor.2. **Unpivot Columns:**
- In the Power Query Editor, select both `Value 1` and `Value 2` columns by holding the `Ctrl` key and clicking on each column header.
- Right-click on one of the selected column headers.
- Choose "Unpivot Columns" from the context menu. This will transform the table by unpivoting the selected columns.3. **Rename Columns:**
- After unpivoting, you'll have three columns: `Item`, `Attribute`, and `Value`.
- Rename the `Value` column to `Final Value` by double-clicking on the column header and typing the new name.4. **Remove the `Attribute` Column:**
- Select the `Attribute` column.
- Right-click and choose "Remove" to delete this column, as it's not needed in the final output.5. **Close and Apply:**
- Click on "Close & Apply" in the top-left corner to apply the changes and return to the main Power BI interface.Your final table in Power BI should now look like this:
```
Item Final Value
A String 1
B String 1
C String 2
D String 3
E String 1
B String 4
D String 5
```Here’s a step-by-step example to illustrate the process:
1. **Initial Data in Power Query Editor:**
```
Item Value 1 Value 2
A String 1
B String 1 String 4
C String 2
D String 3 String 5
E String 1
```2. **After Unpivoting `Value 1` and `Value 2` Columns:**
```
Item Attribute Value
A Value 1 String 1
B Value 1 String 1
C Value 1 String 2
D Value 1 String 3
E Value 1 String 1
B Value 2 String 4
D Value 2 String 5
```3. **Rename `Value` to `Final Value`:**
```
Item Attribute Final Value
A Value 1 String 1
B Value 1 String 1
C Value 1 String 2
D Value 1 String 3
E Value 1 String 1
B Value 2 String 4
D Value 2 String 5
```4. **Remove `Attribute` Column:**
```
Item Final Value
A String 1
B String 1
C String 2
D String 3
E String 1
B String 4
D String 5
```Following these steps will help you achieve the desired transformation of your table in Power BI.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!Thanks for the solution ! The only problem is that it's a calculated table from Power BI so I cannot use the Power Query...
output :
try this calculated table :
Table 2 =var ds_1 =filter(SUMMARIZE(items,items[Item],items[Value 1]),items[Value 1] <> blank())var ds_2 =filter(SUMMARIZE(items,items[Item],items[Value 2]),items[Value 2] <> blank())
RETURNUNION(ds_1,ds_2)let me know if this helps.
