Microsoft® PowerPivot for Excel® 2010

Errata for Microsoft® PowerPivot for Excel® 2010




The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed, PDF, Safari Books Online, Other Digital Version
Page 11-12

The result I received from the query that you show in figure 1-12 is correct because there ARE relationships established in the source data.

Note from the Author or Editor:
In reality the problem is at page 8, you should load two tables (Sales Order Header and Sales Order Detail) together and then import the Product table afterwards, in a different import operation. In this way, only the relationship between the two Sales Order tables will be created and not the one between Sales Order Detail and Product.

Brendan Hager  Jan 29, 2012 
Printed, PDF, Safari Books Online, Other Digital Version
Page 17
4th paragraph

Report CH02-01-FirstSample.xlsx in the companion content is not pivoted to match how you have it pivoted in figure 2-1 on page 17. Also, OnlineOrderFlag returns 0 and -1 for me, not True or false. I have just started this book and am disappointed so far. I hope it's something that I'm doing wrong.

Note from the Author or Editor:
The file CH02-01-FirstSample.xlsx contains the final workbook you obtain at the end of modifications described in Figure 2-16. You can easily create the same starting point by using the raw data you started with in Chapter 1. You can also use the CH01-02-PowerPivot.xlsx as a starting point for PowerPivot workbook, creating a new PivotTable following instructions in the paragraph preceding Figure 2-1.

Brendan Hager  Jan 29, 2012 
Printed, PDF, Safari Books Online, Other Digital Version
Page 18
Third bulleted point

instead of “Placing Color on the rows, Size on the rows” it should be “Placing Color on the rows, Size on the columns”

Note from the Author or Editor:
instead of "Placing Color on the rows, Size on the rows" it should be "Placing Color on the rows, Size on the columns"

Marco Russo
Marco Russo
O'Reilly Author 
Oct 12, 2010  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 50
Very bottom - last line

The last line on page 50 reads: !( [Country] = "USA" && [Quantity] ) Did you mean to say: !( [Country] = "USA" ? Without any reference to the Quantity field? I'm not sure. Thank you! Eric

Note from the Author or Editor:
The last line of page 50 instead of: !( [Country] = "USA" && Quantity) must be: !( [Country] = "USA" )

Anonymous  Oct 22, 2011  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 52
In the "Intellisense" sidebar

The intellisense sidebar says: "When your write a formula in the PowerPivot window, the table name is not displayed by Intellisense when the database contains only one table." However, if I import a single table (for example, FactInternetSales from AdventureWorksDW2008) and type "FactI" in the PowerPivot wondow's formula bar, the autocomplete dropdown contains the table name, "FactInternetSales."

Note from the Author or Editor:
You're right - this behavior changed a little bit since last beta we used during the book writing. Actually IntelliSense works in this way: - if you type an opening square bracket, IntelliSense shows only the columns of the current table (the one in which you are defining the calculated column or the measure) - if you type the first letters of a table name, you will see both table name and column names in IntelliSense

Ron Pihlgren  Nov 23, 2010  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 61
Table 3-2

(might be a typo) In the 2nd paragraph under "Arithmetical Operations," the following is stated: "Moreover, in the very special cases of 0 divided by 0 or Infinity divided by Infinity, PowerPivot returns the special NaN (not a number) value." However, in table 3.2 below the paragraph, the result column shows "Infinity" as the result of 0/0. (PowerPivot does return "NaN" for this case.)

Note from the Author or Editor:
It's a typo. The third row in Table 3-2 (Expression 0/0) has a result of NaN instead of "Infinity" as reported in the table.

Ron Pihlgren  Nov 23, 2010  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 62
List of "BLANK()" equations at the bottom of the page

(maybe a typo) The last two equations in the list: "BLANK() OR BLANK() = BLANK()" "BLANK() AND BLANK() = BLANK()" ...use "OR" and "AND" as logical operators. These will actually produce errors when used. PowerPivot logical operators for OR and AND are "||" and "&&" respectively.

Note from the Author or Editor:
There is a typo in the last two equations at the bottom of page 62. Instead of: BLANK() OR BLANK() = BLANK() BLANK() AND BLANK() = BLANK() There should be: BLANK() || BLANK() = BLANK() BLANK() && BLANK() = BLANK() As an alternative, you might also write: OR( BLANK(), BLANK() ) = BLANK() AND( BLANK(), BLANK() ) = BLANK() The OR and AND syntax in PowerPivot can use both the syntax with DAX operators (||, &&) and the syntax with Excel functions (OR, AND).

Ron Pihlgren  Nov 23, 2010  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 63
List of "BLANK()" equations at the top of the page

(might be a typo) The last 4 equations in the list have the following problems: 1. They use "AND" and "OR" for logical functions. They should use "&&" and "||". 2. They don't show parentheses after the "BLANK" function as all the other equations do. In addition, the result for the last equation is incorrect. If the typos are corrected, the equation will return FALSE: TRUE && BLANK() = FALSE

Note from the Author or Editor:
The last 4 equations in page 63 have a few typos. The last one contains an error (the result is different). Instead of: FALSE OR BLANK = FALSE FALSE AND BLANK = FALSE TRUE OR BLANK = TRUE TRUE AND BLANK = TRUE It should be: FALSE || BLANK() = FALSE FALSE && BLANK() = FALSE TRUE || BLANK() = TRUE TRUE && BLANK() = FALSE An alternative syntax can be: OR( FALSE, BLANK() ) = FALSE AND( FALSE, BLANK() ) = FALSE OR( TRUE, BLANK() ) = TRUE AND( TRUE, BLANK() ) = FALSE In PowerPivot you can use both the syntax for DAX operators (||, &&) and the syntax for Excel boolean operators (OR, AND).

Ron Pihlgren  Nov 23, 2010  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 68
after first paragraph

In the book the formula says [Cost] = SUMX( Sales Sales[Quantity] * RELATED( Product[StandardCost] ) ) in the sample data the formula looks like this: =SUMX( Sales_SalesOrderDetail, Sales_SalesOrderDetail[OrderQty] * RELATED( Production_Product[StandardCost] ) ) This is a small error, but variances between the information in the book and the actual implementation in the companion files occur a number of times throughout the book. I would strongly encourage the authors to review the entire book for formula AND grammatical issues before printing a second edition. The book is full of excellent information, but the writing is awkward, making it difficult for at least this native English speakers to read the book. Extensive use of the word obviously and starting sentences with of course are two examples of words that have no place on the written page. If something is obvious then why are you telling us about it? And of course is just filler text, not useful in a technical book. Starting a sentence with however is bad, bad, bad. However is a modifier, if you must use it, make sure it comes after a comma, not a period. Nevertheless is another word that serves no purpose, it's just padding. I am not an engineer and need books like yours to learn PowerPivot and advanced Excel functionality. Grammatical errors, sloppy language and formula errors make this learning process rather painful. Yours, Debbie Lefkowitz

Note from the Author or Editor:
the formula after the first paragraph references misspelled table names. Instead of: [Cost] = SUMX( Sales Sales[Quantity] * RELATED( Product[StandardCost] ) ) It should be: [Cost]=SUMX( Sales_SalesOrderDetail, Sales_SalesOrderDetail[OrderQty] * RELATED( Production_Product[StandardCost] ) )

Debbie Lefkowitz  Dec 27, 2010  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 104
1st paragraph

In the first paragraph, the last sentence, the book states, "This query can be constructed using the query designer..." However, when I construct the query using the query designer as shown in Figure 4-27, below the paragraph, I get a table with no rows, unlike if I use the hand-built query as shown on page 103 (which returns a table with 19,220 rows). The reason is that, in the AdventureWorks database, no rows in the CustomerAddress.AddressTypeID field that have the value that corresponds to the value for "Primary" in the Person.AddressType table. In the first (non-query designer-generated) query, the restriction is on the right side of a left outer join and hence has no effect but when you design the query in the query designer it applies the restriction in the WHERE clause where it does have an effect. So, I think that you can't generate the same query in the query designer, correct?

Note from the Author or Editor:
You're right, the SQL query shown at page 103 is not identical to the one generated by the wizard shown in Figure 4-27. The intention of the example was to show that you can use the query builder also to create a query with an high number of joins. Unfortunately, the specific SQL query shown at page 103 cannot be created with just the query designer for the use of the "Primary" filter in a LEFT JOIN condition. We cannot reproduce this particular join using the query designer neither by modifying the JOIN condition (that cannot accept comparison with a constant value) nor by adding an OR condition to the existing WHERE condition (which in the current form filter out all rows that would have been included in the first query). We're sorry for this mistake. However, the purpose of the example was just to show a relatively complex query in the designer, even if we wrongly used a condition that cannot be created by using just the designer.

Ron Pihlgren  Nov 30, 2010 
Printed
Page 188
Calculate formula

The formula listed on the page, which is the correct formula is not the same formula as is contained in the sample files. I rewrote the measure to the one in the book and everything came out fine.

Note from the Author or Editor:
The formula shown in the book for the AllContinents measure is correct. The sample workbook contains a wrong formula so if you put the AllContinents measure in the PivotTable as described in the picture you obtain a wrong result, but if you correct the formula with the one shown in the book then the result is correct. The right formula is CALCULATE( SUM( Orders[Amount] ), ALL( Cities[Continent] ) )

Cheryl Cox  Jun 20, 2012 
Printed, PDF, Safari Books Online, Other Digital Version
Page 195
1st and 2nd paragraph

In the first two paragraphs, the column color is referenced as Demo[Color], but it should be Orders[Color]. The name of the table in the example is Orders instead of Demo.

Note from the Author or Editor:
In the second line of the first paragraph instead of "ALL( Demo[Color] )" the expression must be "ALL( Orders[Color] )". In the third line of the second paragraph instead of "ALL( Demo[Color] )" the expression must be "ALL( Orders[Color] )". In the fourth line of the second paragraph instead of "VALUES( Demo[Color] )" the expression must be "VALUES( Orders[Color] )".

Marco Russo
Marco Russo
O'Reilly Author 
Jan 03, 2011  Feb 10, 2012
Printed, PDF, Safari Books Online
Page 207
Table 7-1

In the formulas for FiscalYear and FiscalQuarter the @MonthNumber column is referenced. In the demo file, the same column is named @Month. Thus, in these formulas you need to replace @MonthNumber with @Month to make them work.

Alberto Ferrari
O'Reilly Author 
Dec 21, 2010  Feb 10, 2012
Printed, PDF
Page 220
bottom

Not really an errata but I noticed some pity problems with the ISBLANK function after converting the file in SQL SERVER 2012 format. Example in page 220: Holiday = IF( ISBLANK( RELATED(Holidays[Date]) ), FALSE, TRUE ) now always returns TRUE I changed it in: Holiday = IF( ISNUMBER( RELATED(Holidays[Date]) ), TRUE, FALSE) and it works. Found some reasons but I was not convinced by the Microsoft official explanation: http://connect.microsoft.com/SQLServer/feedback/details/725090/when-isblank-not-blank

Note from the Author or Editor:
This behavior changed in PowerPivot 2012. At page 220 instead of Holiday = IF( ISBLANK( RELATED(Holidays[Date]) ), FALSE, TRUE ) the formula must be changed to: Holiday =IF( RELATED(HolidaysTable[Date]) = BLANK(), FALSE, TRUE )

NGUYEN Yann  Aug 30, 2012  Oct 05, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 301
Last formula in the page (ModelRankTotal)

The calculation of ModelRankTotal can be written in a shorter and faster way: ModelRankTotal =COUNTROWS( FILTER( ALL( Sales[ModelSalesAmount] ), Sales[ModelSalesAmount] > EARLIER( Sales[ModelSalesAmount] ) ) ) + 1

Marco Russo
Marco Russo
O'Reilly Author 
Jan 15, 2011  Feb 10, 2012
Printed, PDF, Safari Books Online, Other Digital Version
Page 304
Formula for ModelRankTotal

The formula of ModelRankTotal can be written in a shorter and faster way: ModelRankTotal = COUNTROWS( FILTER( ALL( Products[ModelSalesAmount] ), Products[ModelSalesAmount] > EARLIER( Products[ModelSalesAmount] ) ) ) + 1

Marco Russo
Marco Russo
O'Reilly Author 
Jan 15, 2011  Feb 10, 2012
Printed
Page 342
formula in middle of page

The book shows the formula name for the measure as "CalculatedOrderFreight =" The workbook on the DVD shows this formula name as: "CourierFreight ="

Note from the Author or Editor:
Correct, the measure should be called CourierFreight in order to replicate the screenshot in Figure 10-35

Anonymous  Jun 23, 2012  Oct 05, 2012