How to UNION more columns in a single column with Power Query

[AgoZ_KH]

I had set a DAX formula like this:

Problems = UNION
(
SELECTCOLUMNS(TABX,"problems",TABX[Pbl_1]),
SELECTCOLUMNS(TABX,"problems",TABX[Pbl_2]),
SELECTCOLUMNS(TABX,"problems",TABX[Pbl_3]),
SELECTCOLUMNS(TABX,"problems",TABX[Pbl_4]),
SELECTCOLUMNS(TABX,"problems",TABX[Pbl_5]),
SELECTCOLUMNS(TABX,"problems",TABX[null])
)

The risult is a new table with a single column like:

Problems

----------

Pbl_1
Pbl_1
Pbl_1
Pbl_1
Pbl_2
Pbl_2
Pbl_2
Pbl_3
Pbl_3
Pbl_4
Pbl_4
Pbl_4
Pbl_4
Pbl_4
Pbl_5
Pbl_5
null
null
null

I want obtain the same result in power query, but as now if I use Query merge (with an empty table) the result table is:

| Pbl | Pbl_1 | Pbl_2 || Pbl_3 || Pbl_4 || Pbl_5 |

 null   Pbl_1   Pbl_2     null     Pbl_4    Pbl_5
 null   null      Pbl_2    Pbl_3   Pbl_4    null
 null   null      null       Pbl_3   Pbl_4    null
 null   Pbl_1   Pbl_2     null     null      Pbl_5
 null   Pbl_1   null        Pbl_3  null      Pbl_5

And if i use Append Query the result table is:

Pbl_1	|Pbl_2 |	|Pbl_3 |	|Pbl_4 |	Pbl_5 |
Pbl_1	null	null	null	null
null	null	null	null	null
null	null	null	null	null
Pbl_1	null	null	null	null
Pbl_1	null	null	null	null
null	Pbl_2	null	null	null
null	Pbl_2	null	null	null
null	null	null	null	null
null	Pbl_2	null	null	null
null	null	null	null	null
null	null	null	null	null
null	null	Pbl_3	null	null
null	null	Pbl_3	null	null
null	null	null	null	null
null	null	Pbl_3	null	null
null	null	null	Pbl_4	null
null	null	null	Pbl_4	null
null	null	null	Pbl_4	null
null	null	null	null	null
null	null	null	null	null
null	null	null	null	Pbl_5
null	null	null	null	null
null	null	null	null	null
null	null	null	null	Pbl_5
null	null	null	null	Pbl_5

What I make wrong?

Can anyone show me how to achieve the same result as the DAX formula in Power Query??

Thanks so much!

AgoZ

[HotChilli]

You'll get a better answer if you post some sample data, then show what the desired result is.

--

I'm a little confused but I 'think' you want to Unpivot the columns from your original table (select all columns from the headers, right-click on the header and the option is there)

[AgoZ_KH]

Hi @HotChilli !

thanks for replay!

I'd more semplified my needs and the example. The unpivot function is not useful in my case. 

Here a graphical example. I have a table like this:

A	B	C	D	E
PB1		PB3	PB4	PB5
PB1	PB2		PB4
	PB2	PB3		PB5
PB1		PB3	PB4
	PB2	PB3		PB5
PB1	PB2	PB3	PB4	PB5
PB1			PB4	PB5

and I need to have from that this Table:

X

PB1

PB1

PB1

PB1

PB1

PB2

PB2

PB2

PB2

PB3

PB3

PB3

PB3

PB3

PB4

PB4

PB4

PB4

PB4

PB5

PB5

PB5

PB5

PB5

In the mainwhile I have found by myself a solution that sound as this:

In Power Query from an empty query I have extracted columns of my interest and created my tables with:

query1= Table.SelectColumns(TableX,{"PB1"})

query2= Table.SelectColumns(TableX,{"PB2"})

and so on.

At this point I can use Append Query and I have opteined my requirements!

AgoZ