Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SachinC
Helper V
Helper V

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

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 ComponentComponent Category
Bonnet CatchEngine Compartment
Bonnet HingesEngine Compartment
Door Locks and OperationBody Exterior Fittings
Fuel Filler Cover/FlapBody Exterior Fittings
Registration PlatesBody Exterior Fittings
Windscreen and GlassBody Exterior Fittings
Brake Pedal OperationBrakes
Brake PipesBrakes
Discs/Pads (if visible)Brakes
Brake Flexible HosesBrakes

.....

 

...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!

 

 

IdDealerIdReport StatusIs Convertible/Has SunroofHas Roof BlindHas Panoramic SunroofHas Turbo/SuperchargerBattery Static VoltageBattery Charging VoltageHas Power Steering ReservoirHas Separate Clutch Fluid ReservoirHas PropshaftHas Rear CV BootsHas Catalytic ConverterHas Diesel Particulate FilterIs AutomaticAble to Road TestPervious Reason For No Road TestReason For No Road TestHas Cruise ControlHas Parking SensorsDistance Driven (5-10 miles)Max Speed Achieved (40-70 mph)Has Locking Wheel KeyHas Start/Stop FunctionHas Headlamp WashersHas Air ConditioningBonnet CatchBonnet Catch Previous NotesBonnet HingesBonnet Hinges Previous NotesDoor Locks and OperationDoor Locks and Operation Previous NotesFuel Filler Cover/FlapFuel Filler Cover/Flap Previous NotesPanoramic RoofPanoramic Roof Previous NotesRegistration PlatesRegistration Plates Previous NotesSunroof/Convertible OperationSunroof/Convertible Operation Previous NotesWindscreen and GlassWindscreen and Glass Previous NotesBrake Pedal OperationBrake Pedal Operation Previous NotesBrake PipesBrake Pipes Previous NotesDiscs/Pads (if visible)Discs/Pads (if visible) Previous NotesBrake Flexible HosesBrake Flexible Hoses Previous NotesBrake Fluid LeaksBrake Fluid Leaks Previous NotesHandbrake Operation
0000691d-b0c4-403d-9e19-7ea98b347a304b806c3c-2121-4712-bfec-466e1c5f15fcFAILFALSEnullFALSETRUE12.414.1FALSEFALSEFALSEFALSEFALSETRUETRUEYes  TRUETRUE550TRUETRUEFALSETRUEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass Pass Pass Pass Pass 
0000f87e-9fd7-4bf2-986c-e7de14555df0cea50b80-736b-48c6-90b7-e66c7b04dba8PASSFALSEnullFALSETRUE12.414.6FALSEFALSEFALSEFALSEFALSETRUEFALSEYes  FALSETRUE970TRUEFALSEFALSETRUEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass PassFront pads lowPass Pass Pass 
00036273-21e2-4421-8de0-16ff959d4d8fcea50b80-736b-48c6-90b7-e66c7b04dba8PASSFALSEnullFALSETRUE12.414.5FALSEFALSETRUETRUETRUETRUEFALSEYes  TRUETRUE970FALSETRUEFALSETRUEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass Pass Pass Pass Pass 
0003ad9c-87e5-4327-9470-757a1766d303f9cce219-6a2d-46e1-98e1-64ba31ac147bFAILFALSEnullFALSETRUE12.515.1FALSEFALSETRUETRUEFALSETRUETRUEYes  TRUETRUE770TRUETRUETRUETRUEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass Fail Pass Pass Pass 
00055cf1-c0f2-4a27-8ebb-6158f14c46d212d534e0-dc6a-4e32-a0ae-2e426ae05612FAILFALSEnullFALSETRUE1214.6FALSEFALSEFALSEFALSEFALSETRUEFALSEYes  FALSEFALSE650FALSEFALSEFALSEFALSEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass Pass Pass Pass Pass 
0005c5b9-d0da-4832-8394-14215eb823c630ad6247-d0b8-4f41-9375-5b9d1842228aFAILFALSEnullFALSETRUE12.114.2FALSEFALSEFALSEFALSEFALSETRUEFALSEYes  TRUETRUE550TRUEFALSEFALSETRUEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass Fail Pass Pass Pass 
0005eba5-8fec-4f97-9415-f89474b6974774320d6c-7546-4b8f-8182-d31d314b5645PASSFALSEnullFALSETRUE12.114.9TRUEFALSEFALSEFALSETRUETRUEFALSEYes  FALSETRUE550FALSEFALSEFALSETRUEPass Pass Pass Pass N/A Pass N/A Pass Pass Pass Pass Pass Pass Pass 
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @SachinC ,

Table:

vyangliumsft_0-1631607113162.png

Table2:

vyangliumsft_1-1631607113164.png

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 .

vyangliumsft_2-1631607113168.png

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:

vyangliumsft_3-1631607113169.png

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @SachinC ,

Table:

vyangliumsft_0-1631607113162.png

Table2:

vyangliumsft_1-1631607113164.png

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 .

vyangliumsft_2-1631607113168.png

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:

vyangliumsft_3-1631607113169.png

 

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
Super User
Super User

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.