Forum Discussion

generaluser's avatar
generaluser
Regular Visitor
9 years ago
Solved

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

  • Sean's avatar
    Sean
    9 years ago

    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

6 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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. 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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? :smileyhappy:

     

    Regards

  • piyushj's avatar
    piyushj
    Frequent Visitor
    9 years ago
    You can also use new tabular table custom visual.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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. 

     

Related content