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!

[v-sgandrathi]

Hi @rmzkk,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. 

We are more than happy to continue to help you.

[v-sgandrathi · Accepted solution]

Hi @rmzkk,

To address this issue, it's important to note that in your raw data, the date is saved as a row within the dataset instead of as a column header. This structure causes Power Query to recognize only the first date block during transposing or unpivoting, while ignoring the rest. To resolve this, you should first locate the rows that contain date values, convert these into a usable date column, and then restructure the data.

Start by adding a new column that identifies date values from the mixed-type column (such as Column5, which may include dates, text like PA, and numbers). Use a try…otherwise expression so that Power Query only converts valid dates, leaving other entries as null. This step helps distinguish the rows that serve as date headers.

Next, fill down the date column so that each record in a date block is assigned the correct date until a new date is found. This step organizes your data into a continuous structure. Afterward, remove the rows that were originally date headers, as they do not represent actual performance records. This leaves you with the relevant Unit, Model, PA, and UA data along with the filled date.

Once the table is structured, perform an Unpivot operation on the PA and UA columns. This will reformat your data so that each row contains a single metric value for each unit and date. Finally, rename the columns as needed, such as Date, Metric (PA/UA), and Value.

Thank you.

[rmzkk]

Hi thanks for your suggestion. Finally solved it by creating multiple query with each period (e.g. 26/01/26 - 31/01/26, 01/02/26 - 07/02/26) and so on then append it to new query.

[v-sgandrathi]

Hi @rmzkk,

Thank you @AlienSx for your answer.

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. 

We are more than happy to continue to help you.

[rmzkk]

Update:

i manage to somehow transform it using unpivot column, but the problem is the date is only from 26/1/26 to 31/1/26, after that date it isn't detected.

the query before unpivot:

the query after i unpivot the column

how do i make certain row (like row 139 which contain date) to make it header in other column so i can unpivot it?

[rmzkk]

Hi, i still lost after the first line of code (which is remove header). here is what the original data look like

below this data there is a table similar like this, but with different date (8 - 14 february 2026)

and here is the query

in column 5 contain multiple data type (date, number, and text)
date: 26/01/26

number: 1, 0.98, etc
text: pa

how do i transform it like this? 

[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