Part V: Understanding Power Pivot and Power Query
As for the Custom Column dialog box, there’s not much to it. The points of input are as
follows:New column name An input box where you enter a name for the column that you’re
creating.
Available columns A list box that contains the names of all the columns in your query.
Double-click any column name in this list box to place it automatically in the formula area.
Custom column formula The area where you type your formula.Just as in Excel, a formula can be as simple as =1 or as complicated as an IF statement
that applies some conditional logic. Over the next few sections, you’ll walk through a few
examples of creating custom columns to go beyond the functionality provided through the
user interface.But before diving in to building Power Query formulas, it’s important to understand how
Power Query formulas differ from those in Excel. Here are some high-level differences of
which you should be aware:■ (^) No cell references: You can’t point outside the Custom Column dialog box to select
a range of cells. Power Query formulas work by referencing columns, not cells.
■ (^) Excel functions don’t work: The Excel functions that you’re used to won’t work in
Power Query. Power Query has many of the same kinds of functions as Excel, but it
has its own formula language.
■ Everything is case sensitive: In Excel, you can type in all-lowercase or all-upper-
case letters and formulas will work. Not so in Power Query. To Power Query, sum,
Sum, and SUM are three different things, and only one of them (Sum) is acceptable.
■ (^) Data types matter: Some fields are text fields, other fields are number fields, while
others are dates. Excel does a good job at handling formulas that mix fields of dif-
fering data types. The Power Query formula language is extremely sensitive to data
types. It doesn’t have the built-in intelligence to handle data type mismatches
gracefully. Data type issues are resolved with conversion functions covered later in
this chapter.
■ (^) No ScreenTips or intelligence help: Excel is quick to throw up a ScreenTip or a
menu of options when you start entering a new formula. Power Query has none of
that. The most Power Query currently offers is a “Learn about Power Query formu-
las” link, taking you to a Microsoft site dedicated to Power Query.
It’s not as gloomy as it sounds. Let’s start with a simple custom column.
Concatenating with a custom column
Earlier in this chapter, you discovered how to concatenate values from two or more col-
umns by using the Merge Columns command. Although the Merge Columns command is easy
to use, it results in the original source columns being removed. There will definitely be
instances when you will want to concatenate values but still retain the source columns.