cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
generaluser Frequent Visitor
Frequent Visitor

Converting multiple columns into 1 column

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:

 

ProductJan-17Feb-17Mar-17Apr-17May-17Jun-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:

 

ProductMonthUnits
Prod AJan-17       7,598
Prod BJan-17          444
Prod CJan-17          241
Prod DJan-17          110
Prod EJan-17          938
Prod AFeb-17       6,595
Prod BFeb-17          341
Prod CFeb-17          213
Prod DFeb-17            26
Prod EFeb-17       3,799
Prod AMar-17     16,542
Prod BMar-17          394
Prod CMar-17          215
Prod DMar-17            12
Prod EMar-17       3,388
Prod AApr-17     13,635
Prod BApr-17          277
Prod CApr-17          374
Prod DApr-17            62
Prod EApr-17       1,226
Prod AMay-17     13,955
Prod BMay-17          277
Prod CMay-17          398
Prod DMay-17            62
Prod EMay-17       1,226
Prod AJun-17       1,401
Prod BJun-17       1,859
Prod CJun-17       2,462
Prod DJun-17       2,573
Prod EJun-17       2,643

 

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

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: Converting multiple columns into 1 column

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

View solution in original post

4 REPLIES 4
Sean Super Contributor
Super Contributor

Re: Converting multiple columns into 1 column

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

View solution in original post

Phil_Seamark Super Contributor
Super Contributor

Re: Converting multiple columns into 1 column

Such a useful function! Smiley Happy


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

v-ljerr-msft Super Contributor
Super Contributor

Re: Converting multiple columns into 1 column

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? Smiley Happy

 

Regards

piyushj Frequent Visitor
Frequent Visitor

Re: Converting multiple columns into 1 column

You can also use new tabular table custom visual.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 3,365 guests
Please welcome our newest community members: