Powerquery function

You could create a pivot Table in PQ, but it may be just as easy to assemble the data with a pivot table. I have attached a file showing both ways to achieve the expected results.