Advice on How To Categorise Data From A Huge Table For Reporting

[SachinC]

Hi,

I have a huge table and the data is spread across many fields, see below. That's just a small snippet, the amount of fields are literally dozens and dozens - I would say 80-100 columns wide.

As you can see, there are fields such as: 

  - "Bonnet Catch" which is categorised as "Engine Compartment" with value Pass/Fail

  - "Brake Flexible Hoses" which is categorised as "Brakes" with value Pass/Fail

I have created a data dictionary in a new PowerBI table, i.e. 

Inspection Component	Component Category
Bonnet Catch	Engine Compartment
Bonnet Hinges	Engine Compartment
Door Locks and Operation	Body Exterior Fittings
Fuel Filler Cover/Flap	Body Exterior Fittings
Registration Plates	Body Exterior Fittings
Windscreen and Glass	Body Exterior Fittings
Brake Pedal Operation	Brakes
Brake Pipes	Brakes
Discs/Pads (if visible)	Brakes
Brake Flexible Hoses	Brakes

.....

...but I haven't a clue how to categorise the fields in my huge table below into the Component Category.

This is because I want to group up all of the Component Categories, i.e.

  - "Brakes" and display Count of Fails, i.e. 80

 - "Engine Compartment" and display Count of Fails, i.e. 69 

     etc...

I maybe playing a bit thick here - probably a simple trick I am missing, but please help!!!

Thanks!

Id

DealerId

Report Status

Is Convertible/Has Sunroof

Has Roof Blind

Has Panoramic Sunroof

Has Turbo/Supercharger

Battery Static Voltage

Battery Charging Voltage

Has Power Steering Reservoir

Has Separate Clutch Fluid Reservoir

Has Propshaft

Has Rear CV Boots

Has Catalytic Converter

Has Diesel Particulate Filter

Is Automatic

Able to Road Test

Pervious Reason For No Road Test

Reason For No Road Test

Has Cruise Control

Has Parking Sensors

Distance Driven (5-10 miles)

Max Speed Achieved (40-70 mph)

Has Locking Wheel Key

Has Start/Stop Function

Has Headlamp Washers

Has Air Conditioning

Bonnet Catch

Bonnet Catch Previous Notes

Bonnet Hinges

Bonnet Hinges Previous Notes

Door Locks and Operation

Door Locks and Operation Previous Notes

Fuel Filler Cover/Flap

Fuel Filler Cover/Flap Previous Notes

Panoramic Roof

Panoramic Roof Previous Notes

Registration Plates

Registration Plates Previous Notes

Sunroof/Convertible Operation

Sunroof/Convertible Operation Previous Notes

Windscreen and Glass

Windscreen and Glass Previous Notes

Brake Pedal Operation

Brake Pedal Operation Previous Notes

Brake Pipes

Brake Pipes Previous Notes

Discs/Pads (if visible)

Discs/Pads (if visible) Previous Notes

Brake Flexible Hoses

Brake Flexible Hoses Previous Notes

Brake Fluid Leaks

Brake Fluid Leaks Previous Notes

Handbrake Operation

0000691d-b0c4-403d-9e19-7ea98b347a30

4b806c3c-2121-4712-bfec-466e1c5f15fc

FAIL

FALSE

null

FALSE

TRUE

12.4

14.1

FALSE

FALSE

FALSE

FALSE

FALSE

TRUE

TRUE

Yes

TRUE

TRUE

5

50

TRUE

TRUE

FALSE

TRUE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Pass

Pass

Pass

Pass

0000f87e-9fd7-4bf2-986c-e7de14555df0

cea50b80-736b-48c6-90b7-e66c7b04dba8

PASS

FALSE

null

FALSE

TRUE

12.4

14.6

FALSE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

Yes

FALSE

TRUE

9

70

TRUE

FALSE

FALSE

TRUE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Pass

Front pads low

Pass

Pass

Pass

00036273-21e2-4421-8de0-16ff959d4d8f

cea50b80-736b-48c6-90b7-e66c7b04dba8

PASS

FALSE

null

FALSE

TRUE

12.4

14.5

FALSE

FALSE

TRUE

TRUE

TRUE

TRUE

FALSE

Yes

TRUE

TRUE

9

70

FALSE

TRUE

FALSE

TRUE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Pass

Pass

Pass

Pass

0003ad9c-87e5-4327-9470-757a1766d303

f9cce219-6a2d-46e1-98e1-64ba31ac147b

FAIL

FALSE

null

FALSE

TRUE

12.5

15.1

FALSE

FALSE

TRUE

TRUE

FALSE

TRUE

TRUE

Yes

TRUE

TRUE

7

70

TRUE

TRUE

TRUE

TRUE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Fail

Pass

Pass

Pass

00055cf1-c0f2-4a27-8ebb-6158f14c46d2

12d534e0-dc6a-4e32-a0ae-2e426ae05612

FAIL

FALSE

null

FALSE

TRUE

12

14.6

FALSE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

Yes

FALSE

FALSE

6

50

FALSE

FALSE

FALSE

FALSE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Pass

Pass

Pass

Pass

0005c5b9-d0da-4832-8394-14215eb823c6

30ad6247-d0b8-4f41-9375-5b9d1842228a

FAIL

FALSE

null

FALSE

TRUE

12.1

14.2

FALSE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

Yes

TRUE

TRUE

5

50

TRUE

FALSE

FALSE

TRUE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Fail

Pass

Pass

Pass

0005eba5-8fec-4f97-9415-f89474b69747

74320d6c-7546-4b8f-8182-d31d314b5645

PASS

FALSE

null

FALSE

TRUE

12.1

14.9

TRUE

FALSE

FALSE

FALSE

TRUE

TRUE

FALSE

Yes

FALSE

TRUE

5

50

FALSE

FALSE

FALSE

TRUE

Pass

Pass

Pass

Pass

N/A

Pass

N/A

Pass

Pass

Pass

Pass

Pass

Pass

Pass

[v-yangliu-msft · Accepted solution]

Hi  @SachinC ,

Table：

Table2：

Here are the steps you can follow：

1. Enter Power Query, select the field that you want to categorize into [Component Category] in your huge list, and select Unpviot Column in Transform. This will generate a new column and place the selected column into it .

2. Create calculated column.

Column =
IF(
    'Table'[Attribute] in SELECTCOLUMNS('Table2',"1",'Table2'[Inspection Component]),
    CALCULATE(MAX('Table2'[Component Category]),FILTER(ALL('Table2'),'Table2'[Inspection Component]=EARLIER('Table'[Attribute]))),"0")

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

You will want to distinguish between object properties ("has sunroof") and test results ("Brake pipes Pass/Fail")

Usually you will also want to unpivot your monster wide table to a simple Id/Key/Value  table  (and keep the dealer ID relationship separate).  Basically convert the source data into something that can be used in a data model.