Microsoft Data Explorer–a review

As you are probably aware, Microsoft released a Preview version of the Data Explorer add-In. It is currently available for Excel 2010 and Excel 2013. Hopefully, this will still be true for the release version (and not only for VL customers).

Mashing up data from different sources has never been the great strength of Excel. Sure, it was possible, but implied a mix of queries formulas, and macros that often led to crappy convoluted workbooks. Data Explorer allows users to create data mash-ups from within Excel, using a single interface and a consistent expression language.

The ribbon

The interface is good, superior to the one provided in PowerPivot, or DQS (other MS tools that allow business users to actively contribute to the information system.)

The seamless integration with Excel is a huge advantage over similar tools (say, Google Refine).

Since the alpha version, things have changed substantially. The Excel add-in now comes with a ribbon, which is clean and efficient, as well as custom task panes in the Modern UI (aka Windows 8) style.

image

You first connect to a source, then shape your query right away. Many sources are available, including tables in the current workbook. You can also reference Data Explorer queries in other queries. This means queries can be re-used. You do not have to rewrite the same query every time. The Merge and Append options allow you to do joins and unions.

Data Explorer accepts hierarchical data sources, not just flat ones. In simple terms, a cell in a column can contain a table. In the query editor, you can navigate to a nested table by double-clicking it. The interface provides a consistent experience across all types of sources.

The editor

The editor was revamped and streamlined. It is intuitive and works smoothly.

image

The ribbon is gone, which is not a bad thing. The formula bar allows you to type expressions, using the “M” language that Data Explorer uses (more on that later). At the time, there is no support for an Intellisense-like feature, and no function wizard as you may know it from Excel.

However, you can do many things from the interface, without ever writing an expression: you can filter, group and transform data in columns … by right-clicking the right object and choosing the relevant transformation. The generated expression can be seen (and edited) in the formula bar.

Each transformation you apply creates a new step in your query. You can rearrange your query by moving steps up or down (except the Source step which you cannot rename or move): did you want to summarize your data, and then filter instead of filtering then summarizing? No problem. Just move the relevant step up or down.

This has a few advantages:

  • The workflow is natural. You can edit one transformation at a time.
  • For every step, you get a visual feedback of the transformation you just applied.
  • This allows you to keep your queries tidy and avoid unreadable nested calculations

When you write your own expressions, you can choose between chaining multiple steps or nesting/combining expressions. The only thing you should actually be worried about is whether your decision will make the workflow easier to read.

You can rename queries from the designer, but also rename each step. As far as I know, this is the only way to document steps in a query.

Adding calculated columns to a query might require you to write expressions. In many use cases, this will be easy. However, some common calculations (dates, string manipulations, …) require a call to a specific function. For the moment, due to the state of the documentation, finding the function you need requires extra work.

I hope Microsoft will not forget to improve the documentation, once the product gets released. The basic documentation could afford some formatting. The language specification documents will probably be too dense for most users.

The expression language

Data Explorer uses the “M” language, which is expression-based.

This means this language is about writing expressions (formulas) just like you would in Excel or DAX. From what I could take from the documentation and quick experimentations, the language is actually extremely powerful.

A bunch of people at Microsoft Research, are very much into functional programing: if you liked LINQ and F#, then you will like the M language. (Microsoft once had a project called Oslo which featured a language  called “M”. Is it the same? I could not recognize it.)

The language supports different types of values: primitive values (string, numbers, …) but also lists, records, tables, and functions.

This means an expression can return a list, or records, or tables, or functions. A data explorer query can return a function, or a table of functions. You can apply fold operations on lists, define functions within a query, create functions that returns functions, or accept functions as parameters … The language supports closure, recursion …

  • Return a list of number:
    Source := { 1 , 2, 3 }
  • Return numbers from 1 to 10:
    Source  := { 1 … 10 }
  • Return a record with A, B, C where C is a calculated column :
    Source := [ A=1,   B=2, C = A + B ]
  • Return a function, and apply it to a value:
    Source := (x) => x + 1
    InvokedSource := Source(12)
  • Return a string that displays numbers from 1 to 10, separated by a comma
    Source := Text.RemoveRange(List.Accumulate({ 1 .. 10 } ,””, (state, acc) => state & “, ”  & Text.From(acc)  ) ,  0, 2)

You could create a query that takes a document as a source, parses the document and returns a function!

What you will be able to do with the language is absolutely huge. However, as I said before, most use cases will not require a full command of the language.

Conclusion

Data Explorer is not Excel: even if you can do  a lot without knowing much of the language, you may need to learn new functions.

However, due to an intuitive interface and a powerful expression language, the tool might appeal to a large audience from the slightly advanced Excel user, to the R addict.

I am not certain it will appeal to all types of developers, though:

  • I do not expect every C# developer to be a fan of functional programing
  • for the moment, Data Explorer is only available in Excel

On the other hand, this will not be a problem if Data Explorer queries (transformations) can be published as data services, or re-used within SSIS packages.

This leads to the following concern: The alpha version was very much about publishing your queries to the cloud. However, I could not find any reference to this scenario, whether in the ribbon or in the documentation. I hope this is an oversight on my part and this feature will be supported.

Data Explorer comes as a bunch of .Net dlls and an interface written in HTML/JavaScript. I really hope the guys at Microsoft will document the libraries, and provide an official entry point so that developers can add custom functionalities to the query designer, or build custom apps on top of the engine.

Finally, Data Explorer might even be too good to just be an Excel add-in. In the future, I could very well imagine never using an Excel formula again (at least if queries could be automatically updated upon data entry.) That summarizes how impressed I was with the product.

Bug in PowerPivot 2012 for Excel 2010

I spotted what I firmly believe to be a bug in PowerPivot. If you already met this problem and/or can reproduce this issue, please support this ticket on MS Connect.

Edit: This bug was fixed with SQL Server 2012 Service Pack 2.

An MS employee might – someday , maybe – respond to my bug report and say this is “by design”. This employee may also advance some pretty good reasons why this is so. However, until then, I cannot conceive of any good reason for this behavior.

Introduction

The data

For the rest of this post, I will use an embedded PowerPivot model based on a linked table. The table is called ‘SomeData’:

Country Product OtherAttribute
USA A Y
FRA B Y
USA C N
FRA C N
UK A N
GER B N
USA A N
FRA B N
FRA D N

What is this bug?

Instead of an elaborate discussion about this bug, let me just start introducing it with a mini-quiz.

Questions

Question 1:

Our model only contains the table ‘SomeData’ presented above. We run the following query against our model.

EVALUATE
CALCULATETABLE(     VALUES(‘SomeData’) ,
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country], [Product]
)
)

What does the query return?

  1. All rows for which Country = “USA” and Product = “A” or Country = “FRA” and Product = “B”.
  2. All rows for which Country = “USA” or Country = “FRA”
  3. All rows for which Product = “A” or Product = “B”.
  4. It depends

Question 2:

Our model only contains the table ‘SomeData’ presented above. We run the following query against our model.

EVALUATE
CALCULATETABLE( ‘SomeData’,
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country], [Product]
)
)

What does the query return?

  1. All rows for which Country = “USA” and Product = “A” or Country = “FRA” and Product = “B”.
  2. All rows for which Country = “USA” or Country = “FRA”
  3. All rows for which Product = “A” or Product = “B”.
  4. It depends

Answers:

Question 1:

1. All rows for which Country = “USA” and Product = “A” or Country = “FRA” and Product = “B”.

Question 2:

4. It depends: The result can be either 2 or 3.

Stating the problem

The only difference between both queries, is the presence (or absence) of the VALUES function in the first argument. This means the expression passed as a first argument to the CALCULATE statement changes the way the 2nd argument is evaluated. This is only supposed to work the other way around.

In the second case, the evaluation of the context is incorrect. PowerPivot will only filter on one column. Which one depends on the order of the columns in the ‘SomeData’ Excel table.

As a result, a query might  have different results depending on how the workbook was built.

A working example (direct filtering) …

Returning an arbitrary set of tuples

Consider the following query:

EVALUATE
SUMMARIZE(
FILTER( ‘SomeData’
, [Country] = “USA” && [Product] = “A”
|| [Country] = “FRA” && [Product] = “B”
)
, [Country], [Product]
)

This query returns the set of (Country, Product) tuples according to my predicates:

SomeData[Country] SomeData[Product]
USA A
FRA B

Filtering data based on a calculated set

If I want to get all rows from ‘SomeData’, where the tuple (Product,  Country) matches one of the tuples in the previous set, I can use the previous table expression as the 2nd argument of a CALCULATETABLE expression. This gives the following query:

EVALUATE
CALCULATETABLE( ‘SomeData’,
SUMMARIZE(
FILTER( ‘SomeData’
, [Country] = “USA” && [Product] = “A”
|| [Country] = “FRA” && [Product] = “B”
)
, [Country], [Product]
)
)

The results:

SomeData[Country] SomeData[Product] SomeData[OtherAttribute]
USA A Y
FRA B Y
USA A N
FRA B N

So far so good.  Everything works as expected.

… that no longer works (cross-filtering)

Returning an arbitrary set of tuples

Instead of explicitly filtering the (Country, Product) tuples, as above, let us now filter our data on the [OtherAttribute] column.

EVALUATE
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country], [Product]
)

As before, this returns:

SomeData[Country] SomeData[Product]
USA A
FRA B

Filtering data based on a calculated set

Let us plug, our new SUMMARIZE expression into our CALCULATETABLE expression.

EVALUATE
CALCULATETABLE( ‘SomeData’,
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country], [Product]
)
)

Unexpectedly, this time, the query returns the following results:

SomeData[Country] SomeData[Product] SomeData[OtherAttribute]
USA A Y
FRA B Y
UK A N
GER B N
USA A N
FRA B N

Note that the engine now returns all rows from ‘SomeData’ for which [Product] is in { “A”, “B” }. This is confirmed by SQL Server Profiler.

Replacing ‘SomeData’ with VALUES(‘SomeData’), however, will return the expected results.

Reordering the columns

Worse, let us just change the order of the columns in our Excel table.

Just drag and drop the [Country] column to the left of the [Product] column, and refresh the PowerPivot model. We have not made any changes to the PowerPivot model itself.

Let us run our previous query again.

The results now become:

Country Product OtherAttribute
USA A Y
FRA B Y
USA C N
FRA C N
USA A N
FRA B N
FRA D N

Which means, the query now only filters by Country. Once again, this is confirmed by SQL Server Profiler.

When a filter context is available

Finally, let us add another CALCULATETABLE expression, so that our query becomes:

EVALUATE
CALCULATETABLE(
CALCULATETABLE( ‘SomeData’,
SUMMARIZE(
FILTER( (‘SomeData’), [OtherAttribute] = “Y”)
, [Country], [Product]
)
)
, ‘SomeData'[Country] = “FRA”)

‘SomeData’ is first filtered by [Country], which only returns the rows for France. These data should then be filtered according to the value in [OtherAttribute], then be summarized by [Country] and [Product]. The one tuple remaining (“FRA”, “B”) should then be used to  filter ‘SomeData’ – overriding our initial filter. This is not the case. Here, the product will only get filtered by [Country].

Once again, replacing ‘SomeData’ in the first argument of the innermost CALCULATETABLE expression with VALUES(‘SomeData’) will produce the expected results.

In that case, however, the result of the query does not seem to depend on the order of the columns in the Excel table.

Measures display the same behavior

Example with COUNTROWS

Do measures display the same behavior? Unfortunately, yes.

Let us consider this measure:

[Fine] :=

    CALCULATE( COUNTROWS( VALUES(‘SomeData’) ),
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country],[Product]
)
)

This measure works as expected:

Fine
A B Total
FRA 2 2
USA 2 2
Total 2 2 4

For the same reasons as in the previous sections, this measure does not:

[Not fine] :=

    CALCULATE( COUNTROWS( ‘SomeData’ ),
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country],[Product]
)
)

The result:

Not fine
A B Total
FRA 2 4
USA 2 3
Total 3 3 6

Once again the grand total may change depending on the order of the columns in the linked table.

Example with SUM

You might think: “Not an issue. I always use VALUES when referencing tables or columns”.

Do  you?

Standard aggregate functions like COUNT, COUNTA, SUM, … only accept a direct reference to a column.

To check this, let us add a new column to our table:

Country Product OtherAttribute CheckColumn
USA A Y 100000000
FRA B Y 20000000
USA C N 3000000
FRA C N 400000
UK A N 50000
GER B N 6000
USA A N 700
FRA B N 80
FRA D N 9

Now, let us observer the same unexpected behavior.

[Sum fails] : =

CALCULATE(   SUM(‘SomeData'[CheckColumn]   ),
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country],[Product]
)
)

The results:

Sum fails
A B Total
FRA 20000080 20400089
USA 100000700 103000700
Total 100050700 20006080 120056780

Note that it should be easy to track which row is in the context for each cell:

For example, the total for FRA is 20400089.  This clearly indicates the last two rows in our previous table (where [CheckColumn] = 80 and 9 ) were included in our table.

Here is one way to make it work as expected.

[Sum works]: =

CALCULATE(
CALCULATE( SUM(‘SomeData'[CheckColumn]   ), VALUES(‘SomeData’)) ,
SUMMARIZE(
FILTER( ‘SomeData’, [OtherAttribute] = “Y”)
, [Country],[Product]
)
)

Sum works
A B Total
FRA 20000080 20000080
USA 100000700 100000700
Total 100000700 20000080 120000780

Slicers and Drill-through in Excel.

While I was playing around with Excel 2013, I noticed some inconsistency in the way slicers behave depending on whether they are used to filter a PowerPivot table, or whether they are used to filter a traditional pivot table.

With traditional pivot tables, slicers do not affect drill-through if they are applied on hidden fields. In other words, if you use a slicer to filter Field1, then you must place Field1 somewhere in your table (row, column, or page) in order for the drill-through to behave as expected.

This is not the case with PowerPivot tables. Drill-through works as expected.

A look at INDEX & MATCH functions

In my earlier post, I looked into the VLOOKUP function and its uses. You may also find more examples this post by Colin Legg. Also, Charles Williams had a nice entry on how VLOOKUP performs, and the difference between exact search and approximate search in terms of algorithm.

In this post, I will focus on the INDEX and MATCH functions and how, used together, they can do the same things as the VLOOKUP functions and sometimes more.

Basics

Introduction

In the following we will use the following reference table, from which we want to extract price information, given some product number.

image

Suppose we want to get the price corresponding to product number XT987. We will first look for the value XT987 in the Product Number column and will get its relative position within the Product Number range. This is where the MATCH function comes into play.

In our case the formula will look like this:
=MATCH(“XT987”, product_number, 0)
We ask Excel to look for the value XT987 in the Product Number column, performing an exact search (that is what the 0 value stands for.) Excel will return the value 2

.image

Once done we will retrieve the price from the same row in the Price column. This is where INDEX comes into play.

In our case the formula will look like this:
=INDEX(price,2)
We ask Excel to return the value found at the 2nd place in the Price column.

image

This is of course possible to do this in one single formula:
=INDEX(price, MATCH(“XT987”, product_number, 0))

Why use INDEX+MATCH instead of VLOOKUP?

Nesting a MATCH function in an INDEX function does not look as nice or readable as a VLOOKUP formula. So what is it good for?

1. Since we can provide a lookup column (product_number) and a result column (price)  independently, we are not limited to using data with the lookup column on the left-hand side of the result column.
This is similar to what the LOOKUP function (not VLOOKUP, not HLOOKUP) can do, except LOOKUP requires your data to be sorted.

2. Actually, we are not even limited to using columns, or ranges. The formula would not change if the lookup and result column were both rows, or one in row one in column. We can also use a formula with hard-coded arrays, if we want:
=INDEX({12,569,321},EQUIV(“XT987”,{“XR134″,”XT987″,”ZL988”},0))

3. The match function supports one additional option for approximate searches, which allows, once again, for more flexibility.

4. Finally, and this is probably the most powerful feature, INDEX returns a reference and not a value. That means, if you used a range for your result column, then you will retrieve a cell reference, which means you can use the result of this formula with functions that take ranges as input values, or with range operators.

The MATCH function

The Syntax

The syntax of the match function is very simple, and will probably remind you of the VLOOKUP syntax.

=MATCH(key_value, lookup_range, lookup_type)

The lookup_value is the value you are looking for. In our example, this will be “XT987”.
The lookup_range will be the range where you are looking for the value. In our case, this will be the column Product Number.
The lookup_type parameter can take the values –1, 0, or 1. When this parameter has the value 0, Excel will perform an exact search. When this parameter is either –1 or 1, Excel will perform an approximate search (more on that later.)

The return value is the position within the lookup range (or array, since MATCH also accepts arrays), where the key value was found.

Lookup types

The look up type parameter accepts 3 values: –1, 0, or 1.

0 means the search will be exact. This corresponds to the search behavior of the VLOOKUP function, when we choose the value FALSE for its last parameter.
1 means the search will be an approximate search, and requires the data to be sorted in ascending order. This corresponds to the default search behavior of the VLOOKUP function, or when we choose the value TRUE for its last parameter.
-1 means the search will be an approximate search, and requires the data to be sorted in descending order. This has no equivalent in the VLOOKUP function.

Suppose your data looks like this and you want to search through the Part Number column.

The MATCH function will return the following values, depending on the value provided for the type parameter.

image

For the type –1, Excel expects data sorted in descending order and, to keep it simple, will return errors most of the time. For the type 0, the value will return an error if the searched value could not be found. For the type 1, Excel will return the last position of the value in the column, or if not found the position of the last available value that is “smaller” than the searched value.

So a value of 0 for the parameter type will return #N/A for a non existing value.

Now, if the data were sorted in descending order

image

the option –1 would return the following results.

image

It will return the first value in the list, that is “larger” than the searched value.

The INDEX function

The Syntax

The INDEX function is actually really complex, and would deserve its own post. Basically, it will take up to 4 parameters, and has two syntax forms
=INDEX(matrix, r)
=INDEX(matrix, r, c)
=INDEX(matrix, r, c, range_index)

This function allows you to fetch a reference to the cell on the r-th row of the c-th column of the input matrix.  To keep things simple, I will not talk about the last parameter in this post.

One important thing to note, is that the function also allows you to fetch a whole row, a whole column, or  even a whole matrix.

For example, the following formula
=INDEX(matrix, 0, 1)
will retrieve the first column of the input matrix.

Optional parameters

Officially, the index function must have at least 2 arguments. However, we may skip any argument past the matrix  and the function will still return valid results. For example,
=INDEX(matrix,,1)
(note the two commas) will return the same result as
=INDEX(matrix,0,1)

The following function
=INDEX(column,)
does the same as
=INDEX(column,0)
and will return our input column.

Multiple lookup with INDEX & MATCH

Suppose we have the following table that tracks the price of different products depending on the year.

image

Now we want to re-use this information in another table. That is, we want to fetch the relevant price, based on some reference to a year value (key_year), and a reference to a product number (key_product).

We will first use the MATCH function to find the first and the last row number for the relevant year, in the Year column.

Let us define a named calculation for the index of the first row where key_year can be found
first_cell_index =MATCH(key_year,year,0)

Let us define a named calculation for the index of the last row where key_year can be found
last_cell_index =MATCH(key_year,year,1)

Given these formulas, and using the fact that INDEX returns cell references, we are now able to fetch the relevant Product Number and Price ranges.

Let us start by using our named calculations in the index functions, to define two new named calculations.
For the first relevant cell in the Product Number range
first_cell_product = INDEX(product_number, first_cell_index )
and for the last cell:
last_cell_product = INDEX(product_number, last_cell_index)

The relevant range can then be constructed by using the range operator “:. This gives the following formula:
range_product  =first_cell_product : last_cell_product

Similarly, we can define the relevant range for the price
first_cell_price = INDEX(price, first_cell_index )
last_cell_price = INDEX(price, last_cell_index )
range_price = first_cell_price : last_cell_price

Using the above named calculations and applying an INDEX + MATCH formula again, we can now match the relevant information for the given key_product.
=INDEX(range_price,
MATCH(key_product, range_product,0)
)

A look at the VLOOKUP function

Since Bill Jelen (MrExcel) launched a VLOOKUP week, I figured it was a good occasion to take a look at this function and compare it with the INDEX/MATCH alternative. In order to prevent my post from being too long, I decided to dedicate this entry to the VLOOKUP function only.

Basics

What the function does

What is the VLOOKUP function about? It allows you to search for a key in a table and find the corresponding value in another column on the corresponding row.

Suppose you have the following reference table for product prices and you want to look for the price of product XT987.

image

First, you would look for the value XT987 in the first column and find the row where this value is.

image

Then, you would move to the second column (the price column) to retrieve the desired value.

image

The syntax

This is what the VLOOKUP function does for you. The function has two syntax forms and comes in two flavors.

=VLOOKUP(key_value, lookup_table ,result_column_index, approximate_search)
=VLOOKUP(key_value, lookup_table ,result_column_index)

key_value is actually the value we are looking for. In my example, this would be XT987.

lookup_table would be our reference table.

result_column_index would be the index our price column (that is, 2).

approximate_search is an option specifying how Excel will look for the value. This parameter tells Excel to perform an approximate search or not. It takes the values FALSE or TRUE and is optional (see the second syntax form), the default value being TRUE. Note that the option TRUE also requires you to sort your table in ascending order (of the key value) for the function to work properly.

Usually FALSE is the option you want to use. Let us see what the difference is.

Approximate or exact search

The case for an exact search

Suppose you have a product number Y5678 and want to find the corresponding price in your table. Now, as you may have noticed, this product is not in our table. Depending on the value you chose for the last parameter, you will get different results.

For an exact search, the formula will look like this:

=VLOOKUP(“Y5678”, lookup_table ,2, FALSE)

and Excel will return #NA since it could not find any corresponding value.

For the  approximate search, the formula will look like this:

=VLOOKUP(“Y5678”, lookup_table ,2, TRUE)

or like this:

=VLOOKUP(“Y5678”, lookup_table ,2)

In that case, Excel will go through the product numbers and will look for Y5678 or the last value that is “smaller” than Y5678. In our case, XT987 is the last value in our Product Number column that comes before Y5678 in alphanumeric order.

Excel will thus return 569, which may not be the result you expected.

The case for an approximate search

Interval searches

Approximate search is well suited for interval searches.

Suppose you have the following table.

image

You want to associate the corresponding shipment fee with each product.

Suppose the shipping price of a product is based on its weight.

If a product weights less than 5 kg shipment will cost 5€.
If a product weights no less than 5 kg but less than 20 kg shipment will cost 20€.
If a product weights no less than 20 kg but less than 90 kg shipment will cost 45€.
If a product weights more than 90 kg shipment is not available.

Then you could use the following lookup table to map the min weight to the shipment fee:

image

Then the following equivalent formulas will give you expected results

=VLOOKUP(weight, lookup_table_shipment, 2)
=VLOOKUP(weight, lookup_table_shipment, 2, TRUE)

When applied to the table, this will give the following results.

image

Let us take the product ZLM988 as an example. The weight of this product is 86 kg. Excel will look for the last key (the minimum weight) in the lookup table, that is equal to or smaller than

86. The corresponding entry is 20. Excel will then return the shipment on the corresponding row and will thus correctly return 45.

Partial searches

This problem comes from a discussion group on a well-known professional networking platform. The original poster was looking for a way to categorize products based on their product number. Now this series number would start with letters just as in my previous examples. Now the part number could have between 1 and 3 characters (I guess, maybe more in the future) and the list of part numbers would be updated regularly. People provided some smart sophisticated solutions, but none had come up with most simple one. Which is to use the VLOOKUP function.

Now, let us take our last table, and categorize these products based on their part number.

image

For this, we will use the following lookup table.

image

Now, one of the following formulas will do the job:

=VLOOKUP(product_number, lookup_table_categories, 2)
=VLOOKUP(product_number, lookup_table_categories, 2, TRUE)

and will return the expected results:

image

Be cautious though. Since we are using an approximate search, we may still have the issue that some part number is missing in the lookup table, thus leading to an incorrect result. Suppose you add another product XSM654 to the data table, whose part number XSM corresponds to the Electronic Devices category. If this part number is not available in our lookup table, the last entry whose value precedes XSM654 in alphanumerical order, is XT. Excel will legitimately return an incorrect category (Books).

You will thus have to implement some formula, to catch these sort of errors.

Note though that you can also use VLOOKUP to retrieve the part number from the lookup table. In our last example, the formula
=VLOOKUP(product_number, lookup_table_categories, 1)
will return XT, when the product_number equals XSM654.

This allows to test whether our lookup table has been correctly maintained or not. For example, you could use the following formula to test whether or not product_number actually starts with the part number returned by the VLOOKUP function:
=SEARCH(VLOOKUP(product_number,lookup_table_categories,1),product_number) = 1

Streaming Office 2010 and Click-to-run

I realize my comment is about 2 years late, but I just had the visit of salesmen trying to sell me a better internet connection. Since I have had no broadband limitation whatsoever in the recent months given the actual content offer, I declined. Furthermore these guys could have been a pair of WLAN free-riders attempting some social engineering scheme.

This, however, led me to the thought I may have to upgrade my connection, should the streaming of applications become prevalent.

It then stroke me that Microsoft Office 2010 already offers streaming capabilities. Microsoft called their technology Click-To-Run, and is already available for Office 2010.

I recently had to re-install Office 2010 and first chose the option. The installation process is shortened to nothing and you can start using your applications straight away. That is absolutely fantastic in principle, and also for practical purposes if you do not use any out of process add-ins.

Unfortunately, I had to re-install the Click-To-Run version with an old-school full install, because it would not work with Powerpivot, for example. I hope Microsoft will find a way to make this sort of installation work with all these add-ins that are out there, including their own. Otherwise, the technology may well fall into oblivion.

%d bloggers like this: