Help Transforming this Audit Inspection Data

Question

The data is in this format (shortened for simplicity, but there are 20+ questions/corrections and additional identifying columns):

Manager	Date	Region	Site Name	Question 1	Correction	Question 2	Correction	Question 3	Correction
John Smith	1/15/2023	West	ABC Facility	No	Fix XYZ	Yes		No	Call Locksmith
George Foreman	1/23/2023	Southeast	123 Plant	Yes		No	Fix Signage	Yes

The response for each question is Yes or No. If No is selected, a correction is filled out. (For example: Is the fire extinguisher working? If no is selected, the correction next to the question would be "Fix fire extinguiser") If Yes, Correction is left blank.

I need to transform the above data to the following format in preparation for a Power Automate import to a SharePoint list:

Site Name	Correction	Date	Region	Manager
ABC Facility	Fix XYZ	1/15/2023	West	John Smith
ABC Facility	Call Locksmith	1/15/2023	West	John Smith
123 Plant	Fix Signage	1/23/2023	Southeast	George Foreman

If there is a correction, then it needs to extracted into its own row accompanied by the identfiying columns (site name, date, etc.)

I tried a column to row transformation and pivoting the Correction columns but I can't get it to look the way I want. Any ideas? Thank you!

anonymous · Accepted Answer

Hi&nbsp;hazwoper&nbsp;,
&nbsp;
You could try unpivotting columns.
Select the following columns, and then click "Unpivot Other Columns".

Filter out "Yes", "No" and blank.

Remove the Attribute column and below is your expexted result.

&nbsp;
&nbsp;
&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Help Transforming this Audit Inspection Data

1 Reply

Related content

Table Transformation

Mirrored Databases Auditing

Network Protection Audit

Transform columns / type

Data transform using power query