Change layout of data from data source

[Stu_22]

Hello, 

I'm fairly new to Power BI and struggling with an Excel data source that isn't laid out in the format I require. I'm using Power BI Desktop and have had a play around with the Edit Queries / Transform data function to no avail. Hoping there is a simple method to manipulate the data!

Data is currently set out as follows...

• Column headers = Inspection; w/c 03/10/22; w/c 10/10/22; w/c 17/10/22; w/c 24/10/22;
• Row 1 = Inspection 1; Completed; Incomplete; Completed; Completed;
• Row 2 = Inspection 2; Incomplete; Completed; Completed; Incomplete;
• Row 3 = Inspection 3; Completed; Completed; Incomplete; Incomplete;
• Row 4 = Inspection 4; Completed; Completed; Incomplete; Incomplete;

I would like the data to be set out in a format as follows..

• Column headers = Inspection; w/c; Complete or Incomplete?
• Row 1 = Inspection 1; w/c 03/01/22; Completed
• Row 2 = Inspection 1; w/c 10/01/22; Incomplete
• Row 3 = Inspection 1; w/c 17/01/22; Completed
• Row 4 = Inspection 1; w/c 24/01/22; Completed
• Row 5= Inspection 2; w/c 03/01/22; Incomplete
• Row 6 = Inspection 2; w/c 10/01/22; Completed

... and so forth.

Any help would be much appreciated!

Thanks,

Stuart

[Stu_22]

Thanks very much Pete - that's worked a treat!

Regards,

Stuart

[BA_Pete · Accepted solution]

Hi @Stu_22 ,

In Power Query, select your [Inspection] column, then go to Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.

This should do what you want.

Pete