Convert single row data with multiple columns into multiple-row data with only two columns

Using Power Query/Get and Transform found on the Data Tab, Highlihgt your table and place in PQ. Highlight the first column. Click on transform Tab, select Unpivot. Select Unpivot other Columns. Delete the middle column.

Here is the resulting Mcode from that action.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Song Code] <> null)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Song Code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

Excel 2016 (Windows) 32 bit

	A	B
1	Song Code	Value
2	USBAS0000001	Irvin Mayfield
3	USBAS0000008	Bill Summers
4	USBAS0000008	Victor Atkins
5	USBAS0000008	Manuel Lander
6	USBAS0100001	W.C. Handy
7	USBAS0100001	Walter Hirsch
8	USBAS0100002	Kermit Ruffins
9	USBAS0100003	Wilmore Jones
10	USBAS0100003	Fleecy Moor
11	USBAS0200045	Edwin B. Edwards
12	USBAS0200045	James D. La Rocca
13	USBAS0200045	Henry Ragas
14	USBAS0200045	Anthony Sbarbaro
15	USBAS0200045	Larry Shields
16	USBAS0200045	Carl Sigman
17	USBAS0200046	Dr. Michael White
18	USBAS0200049	Thurland Chattaway
19	USBAS0200049	F. A. Mills
20	USBAS0200060	Ray Manzarek
21	USBAS0200060	John Densmore
22	USBAS0200060	Robert Krieger
23	USBAS0200060	Jim Morrison
24	USBAS1400111	Paul Blaize
25	USBAS1400111	Rodney Williamson
26	USBAS1400111	Steven Workman
27	USBAS1400111	Phil Frazier
28	USBAS1400111	Keith Frazier
29	USBAS1400111	Derrick Tabb
30	USBAS1400111	Stafford Agee
31	USBAS1400111	Gregory Veals
32	USBAS1400111	Derrick Shezbie
33	USBAS1400111	Chadrick Honore
34	USBAS1400111	Vincent Broussard
35	USBAS1400416	Joseph Modeliste
36	USBAS1400416	Arthur Lanon Neville
37	USBAS1400416	Cyril Garrett Neville
38	USBAS1400416	Leo Nocentelli
39	USBAS1400416	George Joseph Porter Jr.
40	USBAS1400417	Harold Raymond Battiste, Jr.
41	USBAS1400418	Wayne Shorter
42	USBAS1400426	Kurt Weill
43	USBAS1400426	Eugen Berthold Brecht
44	USBAS1600204	John Lennon
45	USBAS1600204	Paul McCartney
46	USBAS1800106	George Gershwin
47	USBAS1800106	Ira Gershwin
48	USBAS1800106	DuBose Heyward
49	USBAS1800106	Dorothy Kuhns
50	USBAS1800107	Dr. Michael White
51	USBAS1800108	Luther Dixon
52	USBAS1800108	Al Smith

Sheet: Sheet1

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

Power Query is not an option for Mac users. It is only available to Excel Windows version 2010 and later.