Forum Discussion
Select Certain Row to Transform Into Column
Hi Anonymous,
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.
Hi Anonymous,
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.
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.