Creating a combined cells to reduce the number of rows

[craigs85]

Hi everyone.

I have a large sheet that consists of car parts from a particular brand. Rather than it being one row per part number, the part number is on multiple rows, one for each different vehicle variant that it can fit.

There are around 165000 rows of parts that are available for various makes, models of cars etc.

Columns A to F are the car makes, models, years etc.
Column G is the Part Number.

Is it then followed by a load more other columns that are the same for each part number.

I want one row for each unique Part Number, but then a cell on each row that is a pipe seperated list of all the combined values or row a (makes) that have the same part number, then another cell with a list of all the models and so on.

This is very similar to a topic I raised late last year where it was resolved by a very helpful member on this community. (Merge cell contents based on a shared value)

Not being a programmer, I couldn't understand it enough to adapt it for this new file, although I would love to be able to.

If anyone can help,that would be great.
Can provide more info if needed!

Thanks in advance,
Craig

 

[sandy666]

don't use special characters in column names - stay with text and numbers
in Power Query Editor on right side you can see steps, click each step then you can see how your table will change
almost each step has a little gear on the end of the step name, try to click it and see what is inside this step
so you can do similar steps for Details and Price - just add Custom Column and use the same code and change the columns name in the code accordingly
after that do this

 

[sandy666]

here is almost everything about Power Query

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[craigs85]

Ok will give it a good read.

Thank you so much for your help. I really appreciate it.

 

[sandy666]

You are welcome and thanks for the feedback
Have a nice day