Power Query: Efficient Data Transformation Required

I'm looking for help with transforming data from Table 1 to Table 2 in the attached example.

I'm setting up some data quality checks where users can paste in their source data and hit refresh all to populate a dashboard where areas of concern are highlighted

There are a lot of checks involved, complex queries and large data sets.

I've reached the point where attempting to edit some of these queries is causing excel performance issues so I am trying to find more efficient ways of transforming data to enable these checks

One of the more complex checks I have set up is detailed in the attached spreadsheet.

I am hoping someone here with more power query experience than me can help make this transformation more efficient.

The transformation I am looking to improve upon is to turn Table 1 into Table 2

This is a simplified example of what I'm trying to do.

My current solution involves 5/6 queries and very many steps. I have not been using power query for very long so I'm sure there will likely be some more efficient ways to do this transformation.

Any help would be appreciated. (There are a few additional notes on the attached spreadsheet)


Thanks