Creating a new table in Power BI combining columns

[Jannis68]

I need to create a new table combining six columns into two columns and keep the identifiers. The exisiting table is:

question_id	respondent_id	Pos1	Pos2	Pos3	Neg1	Neg2	Neg3
1	1	x
2	1	x
3	1	x
1	2	xx
2	2	xx
3	2	x

The new table should look like this:

question_id

respondent_id

Positive

Negative

Positive should be a union of Pos1, Pos2, Pos3 and Negative Neg1, Neg2, Neg3

Any thoughts?

[v-ljerr-msft · Accepted solution]

Hi @Jannis68,

If I understand you correctly, you should be able to use the Unpivot Columns option in Query Editor to unpivot the Pos columns and Neg columns separately to get the expected result in your scenario.

For more details about how to Pivot and Unpivot with Power BI, you can refer to this article. 

Regards

[Rfranca]

hi, @Jannis68

When do you want to make that combination?
When importing data or when creating a measure?
Because depending on the moment you can use DAX functions or M functions.

Of more details of the moment of this combination.

[Jannis68]

I have no preference wheter using on import or when creating a DAX.

[Zubair_Muhammad]

@Jannis68

Using DAX you can create a calculated Table

Go to Modelling Tab>>>NEW TABLE and use this formula

Table =
SUMMARIZE (
    TableName,
    TableName[question_id],
    TableName[respondent_id],
    "Positive", SUM ( TableName[Pos1] ) + SUM ( TableName[Pos2] )
        + SUM ( TableName[Pos3] ),
    "Negative", SUM ( TableName[Neg1] ) + SUM ( TableName[Neg2] )
        + SUM ( TableName[Neg3] )
)

[Jannis68]

Thanks for input. Actually this is not what I was looking for.  I would like the new table to look like:

question_id	Positive	Negative
1	Pos1	Neg1
1	Pos2	Neg2
1	Pos3	Neg3
2	Pos1	Neg1
2	Pos2	Neg2
2	Pos3	Neg3

[Ashish_Mathur]

Hi,

For further clarity, please take a clearer example.  Instead of xx, show some data under the Pos and negative columns and on that dataset, show your desired result.

[Zubair_Muhammad]

@Jannis68

Using Query Editor,

You can add new columns ...

Select the Columns you want to sum/combine>>>Go to "Add Column" Tab>>>Statistics>>>sum

Picture below shows this