Transform an Unstructured table in Power Query

[shubhamkolekar]

Consider the above table which I have loaded in Power Bi Power Query. 

I want to compare the total number of special consideration applications for each year from 2019 to 2024. The image (Raw Data) shows number of applications for the year 2019 where I have filtered for only first 2 week of 2019 (Week of 04/01/2019 and 11/01/2019). Each week consist of the list of Colleges in column 1 as shown and has 5 columns(Forms, Unit Applications, Right Now, Special Exam and Total Weekly Units) associated with each week.
The dataset contains the list of all weeks data for 2019 with associated 6 columns (Forms, Unit Applications, Right Now, Special Exam and Total Weekly Units) which I have filtered for this instance. What can be the best way to trasnform above table into a meaningful table for visualization. There are many other files for each year (2020,2021, 2022, 2023 and 2024) which has similar data and structure of the table which I will be appending after this transformation to get into one table.
I want to transform the structure of this table in such a way that I should be easily able to compare values for each week or year. In the 2nd figure you can see how each visual is comparing the values for 2019 vs 2020 for each College.  What can be the best way to transform this table in order to get the right columns to filter the visual later.

Please if anyone has any idea on how I can do this would be highly appreciated.

[rajendraongole1 · Accepted solution]

Hi @shubhamkolekar - you have to perform some steps as below:

Steps to Transform the Table:
Unpivot Columns: Since your data contains multiple columns for each week (e.g., Forms, Unit Applications, Right Now, etc.), it's best to unpivot these columns to create a more flexible, tidy structure.

In Power Query, select the columns for the associated week (e.g., Forms, Unit Applications, Right Now, etc.).
Right-click on these columns and select Unpivot Columns.
This will convert these columns into rows with new columns Attribute (for the original column names) and Value (for the corresponding values).
Create a Week and Year Column:

Based on your raw data, create a new column for the week of the year and the year itself. If you don't already have a Year column, extract it from the date.
You can use Power Query's Date functions to create these columns. For example, extract the week number using Date.WeekOfYear([Date]) and the year using Date.Year([Date]).
Merge Datasets for Each Year:

After transforming the data for 2019, you will need to repeat the same transformation for each year (2020, 2021, 2022, 2023, and 2024).
Append the tables for each year into one consolidated table. You can use the Append Queries feature in Power Query to achieve this.
Ensure that each dataset is transformed in the same way so that the resulting table structure remains consistent.

please share sample data for reference