Jan 21 2021 03:24 AM - edited Jan 21 2021 03:26 AM
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!
Jan 21 2021 05:07 AM
@penguin_wai , you may add data in columns D and E to data source with some names and show them in hierarchy.
Jan 21 2021 05:17 AM - edited Jan 21 2021 05:22 AM
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?
Jan 21 2021 06:23 AM
SolutionJan 21 2021 07:17 AM
@Sergei Baklan I have applied power query and successfully solved the problem. Thanks Sergei!
Jan 21 2021 07:22 AM
@penguin_wai , you are welcome
Jan 21 2021 06:23 AM
SolutionI'd use Power Query to transform the data, result will be as