Microsoft Office Tutorials and References
In Depth Information
After you import the table into PowerPivot, you can now appreciate one big difference between
the PivotTable report shown in Figure 10-13 and the PowerPivot table shown in Figure 10-15: the
freight, which was an attribute of the product dimension in the PivotTable, is now a column like
the others in the table and can be used to slice data. PowerPivot does not make any distinction
between measures and attributes; it works on any column.
FIguRE 10-15 The result of the query in Figure 10-14 imported to PowerPivot.
You are moving towards a solution, but you still have problems to solve. The first issue you
need to face is that the weight, as it has been imported, is a string value and not a number.
To summarize it, you definitely need to transform it into a number. Because you cannot
change the data type of the column (trying to do that would raise an error because the
column contains the unknown value), you can add a new column that contains the
conversion of the Weight column into a number. When the value is unknown, you assume a 0.75
value as a default. Please note that this assumption might well be wrong. Nevertheless, for
what concerns us in this example, you can safely do it.
You can now rename the Weight column OriginalWeight and add a new column, named
Weight, which is computed by this formula:
Weight = IF( NOT( ISERROR( VALUE( Orders[OriginalWeight] ) ) ),
VALUE( Orders[OriginalWeight] ),
With this new column in the table, it is now very easy to produce a report such as the one
shown in Figure 10-16, where you are able to aggregate the weight per order. (In the report,
we show the details of products even if, for the purpose of our analysis, it is useless to go
into the details.)