Select Certain Row to Transform Into Column

[rmzkk]

Greetings,

I have a raw data like this, it shows a unit type, unit id, and its performance (PA and UA)  per-date

notice in row 3 there is data containing date, and below it (row 143) there is also a date

i want to transform it to something like this

tried transpose it but it doesn't sync with other data, how can i do it?

Any suggestion will be appriciated, thanks!

[AlienSx]

let
    fx = (tbl) => ((dates) => List.TransformMany(
        Table.ToList(Table.RemoveFirstN(tbl, 1), (x) => List.RemoveFirstN(x, 2)),
        (x) => List.Zip({dates, List.Split(List.RemoveFirstN(x, 2), 2)}),
        (x, y) => {y{0}} & List.FirstN(x, 2) & y{1}
    ))(List.RemoveNulls(List.RemoveFirstN(Record.ToList(tbl{0}), 4))),
    Source = Excel.Workbook(File.Contents("path_to_your_file\data_file.xlsx"), null, true)[Data]{0},
    sel = Table.SelectRows(Source, (x) => x[Column3] <> null),
    group = Table.Group(sel, "Column3", {"x", fx}, GroupKind.Local, (s, c) => Number.From(c = "Unit")),
    z = Table.FromList(List.Combine(group[x]), (x) => x, {"Date", "Unit", "Model", "PA", "UA"})
in
    z