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
1111

Cable

x   
1111Wire x  
1111Heat Shrink1  x 
1111Heat Shrink2   x
2222

Wire

 x  
3333Wire x  
3333Heat Shrink2   x

 

My desired output is:

Finished GoodCut CableCut WireCut Shrink1Cut Shrink2
1111xxxx
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.

 

Thanks

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.