Merging columns

[capko]

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...

[johnbasha33]

@capko 

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 !!

[capko]

Thanks for the solution ! The only problem is that it's a calculated table from Power BI so I cannot use the Power Query...

[Daniel29195 · Accepted solution]

@capko 

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()

)

RETURN

UNION(

    ds_1,ds_2)

let me know if this helps.