What do you want your report to look like. We are not mind readers here. Suggest you mock up a result table so that we know and understand what you need.
Excellent Peter! What guidelines can you give me on how to do this? I would like to learn how to do this with Power Query. Is there some tutorial or reading that you can suggest please?
Hi @SollyEng
The bad news is that I am not the person to guide you since I am a Power Query novice. I suppose the good news is that, if I can transform your data as a novice, so can you!
@zosiano
In one of his videos Oz du Soleil (http://datascopic.net/) made the point that on occasion the correct action is not to work on dirty data; the proper action is to reject it and return it to the originator for correction. If you have to make 'best guesses' to give clean data then you may simply be locking errors into an apparently 'clean' dataset.