Consolidate multiple columns that store the same data

[blindape]

Hi,

I have an accident register, where the particulars of each workplace accident are recorded. (Example file located here: https://docs.google.com/file/d/0BzroIkMHFklTVlVOVm1JeW11cjg/edit?usp=sharing)
An injury can occur to multiple locations so the forms that record the injury have multiple injury location columns. (see image below).

A single injury location can occur in one of the five Location columns (e.g. Arm/wrist occurs in both Location1 and Location2).

In order to analyse this with traditional PivotTables, I have had to create calculated columns (see below) to the right of the raw data, one for each injury location. This allows me to add each location to the PivotTable as a separate field. However, because I need a separate field for each injury type, I can't filter / Slice a PivotTable by injury location.

I have imported the data table into PowerPivot and tried several different methods to be able to filter by injury location, but with no success. From what I have read/tried, I believe I would need to set up a disconnected table of injury locations and then filter the data via a Measure in the PivotTable as creating a link between the table of locations and the main table, won't work as I need to link multiple columns in my main table with the injury location table.

So I have two question, the most critical is "Is it even possible to be able to set up this behaviour in PowerPivot? and if so how?".
The second is more of a curiosity question: "is it better to have calculated columns in source data tables as above, or in the PowerPivot table?"

Thank you in advance.
(Note: I'm not sure how those images will be shown as the preview only displays an Icon. however, right-clicking and choosing open image in new tab displays ok.)

Regards, John

 

[Jacob Barnett]

PowerPivot can definitely deal with this scenario but the crucial thing is to get the structure of your data right. As you've discovered your current 'tabular' approach is troublesome and in my opinion you should be trying to shape your data either on or prior to import in order to be able to build a more effective data model.

Ideally your data would look something like:

If your data source is Excel then you've basically got 2 options - VBA or Download Data Explorer for Excel - Office.com which you can use to shape the data prior to import. If you are getting the stuff from a database then it may be possible to do it during import.

I'd be happy to give you some pointers on a VBA approach if you are interested.

 

[blindape]

Hi Jacob,

Thanks very much for your reply. I had a feeling the answer might be something like this. Yes the source data does come from an Excel table.

Thanks for your suggestion of using Data Explorer. After 10 min of playing (I mean working) in Data Explorer, I was able to create the following pivot-friendly table:

I could have come up with the VBA to do the same job but that would have been half a days work. The Data Explorer option is much quicker.

You help has been much appreciated.

Regards, John