Power Query - split rows of data from multiple columns into rows with respective value on a separate line

[katsdelite]

Hello,

I'm new to Power Query and have tried to create this but I keep getting so far and get stuck. I have an excel spreadsheet that has data listed by employee number in column A, with columns of data for each employee. I want to transform that data and parse it out to have one line for each employee with their own data under the respective column.

Thanks in advance!

Original

Desired

 

[AlienSx]

let
    ladder = (lst) => ((first) => List.Transform({1..List.Count(lst) - 1}, (i) => {first} & List.Repeat({null}, i - 1) & {lst{i}}))(List.First(lst)),
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    to_list = Table.ToList(Source, ladder),
    to_table = Table.FromList(List.Combine(to_list), (x) => x, Value.Type(Source))
in
    to_table

 

[AlienSx]

v2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    to_list = Table.ToList(
        Table.UnpivotOtherColumns(Source, {"Employee Number"}, "a", "b"),
        (x) => Record.FromList({x{0}, x{2}}, {"Employee Number", x{1}})
    ), 
    tbl = Table.FromRecords(to_list, Value.Type(Source), MissingField.UseNull)
in
    tbl

 

[katsdelite]

Thank you, thank you, thank you! I've spent days trying to figure out how to do it and you spit it out like it's nothing lol

How can I have it delete empty lines in PQ - or is it best to do that in the output?

 

[katsdelite]

I figured it out and it works beautifully! Thanks again, I really appreciate it!