Converting multiple columns into 1 column

[generaluser]

Hi,

I would like to group multiple columns into one column. I have the product name in column A, and then the units by month in the columns after. I would like to keep column A as the product name, have column B as the month and then column C as the units.

An example below of current data format:

Product	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17
Prod A	7,598	6,595	16,542	13,635	13,955	1,401
Prod B	444	341	394	277	277	1,859
Prod C	241	213	215	374	398	2,462
Prod D	110	26	12	62	62	2,573
Prod E	938	3,799	3,388	1,226	1,226	2,643

An example below of desired data format:

Product	Month	Units
Prod A	Jan-17	7,598
Prod B	Jan-17	444
Prod C	Jan-17	241
Prod D	Jan-17	110
Prod E	Jan-17	938
Prod A	Feb-17	6,595
Prod B	Feb-17	341
Prod C	Feb-17	213
Prod D	Feb-17	26
Prod E	Feb-17	3,799
Prod A	Mar-17	16,542
Prod B	Mar-17	394
Prod C	Mar-17	215
Prod D	Mar-17	12
Prod E	Mar-17	3,388
Prod A	Apr-17	13,635
Prod B	Apr-17	277
Prod C	Apr-17	374
Prod D	Apr-17	62
Prod E	Apr-17	1,226
Prod A	May-17	13,955
Prod B	May-17	277
Prod C	May-17	398
Prod D	May-17	62
Prod E	May-17	1,226
Prod A	Jun-17	1,401
Prod B	Jun-17	1,859
Prod C	Jun-17	2,462
Prod D	Jun-17	2,573
Prod E	Jun-17	2,643

Does anybody know how to quickly do this using Power Query?

Thanks

[Anonymous]

Hi. 

I have a similar situation. 

Column A Column Sales1   Column Sales 2    Sales Total 

BEN                 NULL                       400

LISA                 1000                        NULL

MEG                 2500                       NULL

I would like to have Total Sales - wather it is sum or not it doesnt matter, as the columns Sales1 and Sales2 are DAX(calculated) columns- I habe already sum from different tables. Any ideas on how i can make ONE column with the sales of each person=?

Thank you and looking forward. 

[piyushj]

You can also use new tabular table custom visual.

[v-ljerr-msft]

Hi @generaluser,

Have you tried the solution provided above? It should work in your scenario. If you still have any question on this issue, feel free to post here.

In addition, if the solution works, could you accept it as solution to close this thread? 

Regards

[Sean · Accepted solution]

In the Query Editor

1) select the Product Column

2) Transform tab - Unpivot Columns - Unpivot Other Columns

3) Rename the Attribute and Values columns - Month and Units

[Anonymous]

Hi Sean, I have a table with 126 columns, that I need to be able to split out 60 columns and have them then line up based on the GUID.  They are the details that are attributed to each IR # that is entered based on a date and station ID. 

[Phil_Seamark]

Such a useful function! 🙂