Table transformation

Hi Elad_Magazanik 

With Get & Transform aka Power Query in attached file. Done with the UI/Menus only

Alternative:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    RemovedBlankLocation = Table.SelectRows(Source, each
        not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    ),
    FilledDownItem = Table.FillDown(RemovedBlankLocation, {"Item Number"}),
    TranposedLocations = Table.Group(FilledDownItem, {"Item Number"},
        {
            {"LOCATIONS", each Table.Transpose(Table.SelectColumns(_, "Location")), type table},
            {"LOC_COUNT", Table.RowCount, Int64.Type}
        }
    ),
    ColumnsToExpand = List.Transform({1..List.Max(TranposedLocations[LOC_COUNT])},
        each "Column" & Text.From(_)
    ),
    RemovedLocCount = Table.RemoveColumns(TranposedLocations, {"LOC_COUNT"}),
    ExpandedLocations = Table.ExpandTableColumn(RemovedLocCount, "LOCATIONS",
        ColumnsToExpand, List.ReplaceValue(ColumnsToExpand, "Column", "Location ", Replacer.ReplaceText)
    )
in
    ExpandedLocations