Consolidate Rows With Power Query

Copper Contributor

I'm trying to make a scheduling spreadsheet to keep track of tasks at work. I have access to a file with our finished products and the components that are required, which I am using to populate the required tasks to be done. Basically, it is formatted as:


Finished GoodComponentCut CableCut WireCut Shrink1Cut Shrink2


1111Wire x  
1111Heat Shrink1  x 
1111Heat Shrink2   x


3333Wire x  
3333Heat Shrink2   x


My desired output is:

Finished GoodCut CableCut WireCut Shrink1Cut Shrink2
2222 x  
3333 x x


What is the best way to achieve this? I have simplified this a lot, and not all components have a required task associated. The file I am using has around 250K rows.



1 Reply

@DylanG Although, I've never tried a pivot table on a 250K rows data set, such a table could handle the task. But since you already are using Power Query, it takes only three steps to achieve the same.


Both are demonstrated in the attached workbook.