How to move horizontal data to vertical line items?-CodePudding

I created an Excel table with different Inventory items saved in different physical locations. This table was compiled from various Inventory tables.

My table looks like this:

-------------------------------------------------------------------------
|Item Name|Item Description|Qty loc1|Qty loc2|Qty loc3|Qty loc4|Qty loc5|
-------------------------------------------------------------------------
|Item 1   |Item 1 Descr    |       2|       4|       5|       3|       1|
-------------------------------------------------------------------------
|Item 2   |Item 2 Descr    |       8|        |       5|        |       1|
-------------------------------------------------------------------------
|Item 3   |Item 3 Descr    |        |       4|        |       3|       1|
-------------------------------------------------------------------------
|Item 4   |Item 4 Descr    |       4|        |       5|        |       1|
-------------------------------------------------------------------------
|Item 5   |Item 5 Descr    |        |        |        |        |        |
-------------------------------------------------------------------------
|Item 6   |Item 6 Descr    |      1 |        |        |        |      3 |
-------------------------------------------------------------------------

I would like my table to look like this:

----------------------------------------------
|Item Name|Item Description|Qty     |Loc Name|
----------------------------------------------
|Item 1   |Item 1 Descr    |       2|    Loc1|
----------------------------------------------
|Item 1   |Item 1 Descr    |       4|    Loc2|
----------------------------------------------
|Item 1   |Item 1 Descr    |       5|    Loc3|
----------------------------------------------
|Item 1   |Item 1 Descr    |       3|    Loc4|
----------------------------------------------
|Item 1   |Item 1 Descr    |       1|    Loc5|
----------------------------------------------
|Item 2   |Item 2 Descr    |       8|    Loc1|
----------------------------------------------
|Item 2   |Item 2 Descr    |       5|    Loc3|
----------------------------------------------
|Item 2   |Item 2 Descr    |       1|    Loc5|
----------------------------------------------
|Item 3   |Item 3 Descr    |       4|    Loc2|
----------------------------------------------
|Item 3   |Item 3 Descr    |       3|    Loc4|
----------------------------------------------
|Item 3   |Item 3 Descr    |       1|    Loc5|
----------------------------------------------

and so on...

In this case, some locations DO NOT have any items and hence that cell is blank.
An item should only have a line if there is any inventory in any location.

I tried making a separate worksheet in the same workbook to be able to pull cell values but that didn't work. What would be the best approach to tackle this? What would be the formula for this?

CodePudding user response：

Unpivot Using Power Query

Do It Yourself

Right-click on your table and in the pop-up menu select Get Data from Table/Range....

The Power Query Editor opens.

Ctrl left-click Item Description to add it to the selection.

Right-click Item Description and in the pop-up menu select Unpivot Other Columns.

Left-click Value and drag it to the left so the column switches places with the Attributes column.

Right-click Value and in the pop-up menu select Rename... and rename the column to Qty.

Similarly, right-click Attributes and in the pop-up menu select Rename... and rename the column to Loc Name.

Right-click Loc Name and in the pop-up menu select Replace Values... and in the Replace Values dialog, in the Value To Find text box, enter Qty (note the trailing space) and press OK.

Right-click Loc Name and in the pop-up menu select Transform and in the Transform pop-up menu select Capitalize Each Word.

Select Close & Load in the ribbon.

Use the M-Code

Right-click on your table and in the pop-up menu select Get Data from Table/Range....
The Power Query Editor opens.
In the ribbon, select Advanced Editor and replace the current code with the following one (copy the code, click in the box and use Ctrl A and Ctrl V).
Adjust the table name and the column names if necessary.
Select Done.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Name", type text}, {"Item Description", type text}, {"Qty loc1", Int64.Type}, {"Qty loc2", Int64.Type}, {"Qty loc3", Int64.Type}, {"Qty loc4", Int64.Type}, {"Qty loc5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Name", "Item Description"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Item Name", "Item Description", "Value", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Qty"}, {"Attribute", "Loc Name"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Qty ","",Replacer.ReplaceText,{"Loc Name"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"Loc Name", Text.Proper, type text}})
in
    #"Capitalized Each Word"

Select Close & Load in the ribbon.