Analysing Multiple Cells in a Single Field in Pivot Table

[penguin_wai]

Hi all. This is my first post here. I am sorry if I have missed anything.

My problem is that I have a few hundred complaint cases on a fleet of buses regarding their maintenance. My task is to analyse the common maintenance problems and how did the parts malfunctioned. Attached you may find a mini-template similar to my original excel file, which is in Chinese. A bus can have single or multiple problems found in a single defect. For example, if the air-conditioning is insufficient, it can be attributed to broken air-conditioning belt in a scenario and broken air-conditioning belt and dirty condenser in another scenario. I would like to include all the problems found in the pivot table analysis. However, in the attached excel file, I can only include the first problem shown in column C in the pivot table analysis. The remaining problems in columns D and E shown in a red circle are all left out. Is there a way that I can include all the problems found bringing a defect of a bus?

Thanks a lot in advance!

[#2]

@penguin_wai , you may add data in columns D and E to data source with some names and show them in hierarchy. 

[#3]

Hi @Sergei Baklan

Thanks for your reply. I have tried your method. As shown in the attached, the problems in columns D and E are displayed as the sub-problems of those in column C. Is there a way to display the problems in each cell indepedently and counted in the Grand Total?

[#4 · Accepted solution]

@penguin_wai 

I'd use Power Query to transform the data, result will be as

[#5]

@Sergei Baklan I have applied power query and successfully solved the problem. Thanks Sergei!

[#6]

@penguin_wai , you are welcome