Power Query M Primer (Part 6): Types—Intro & Text (Strings)

• , , , ,

Examine an executing Power Query mashup under a microscope and what to you see? Data pulsing down pathways between expressions. Increase your microscope’s magnifying power. As you zoom in on one of the flows, what you’re viewing transforms from a blurred stream of data into the individual data items that make up that flow.

As you study what you see, you notice that the data items flowing by fit into groupings based on the kind of value they contain: some hold text, others are made up of a date, a time or a datetime; yet others are true/false values, then there are numbers…and it looks like there are even more categories beyond these!

In this post, we’ll begin exploring these categories—we’ll begin exploring the kinds of values supported by the M language. In programming parlance, these kinds of values” are called types.

Types can have special behaviors associated with them. For example, date and time have a special rule around addition: add a date and a time together and you get back a datetime! We’ll investigate these special rules.

Also, we’ll discuss the literal syntax associated with each type. In this context, ”literal” implies that you literally want to write out a value in code. You might, for example, literally want to set variable rate to the hand-selected value of 100. The syntax you use to code up the expression that produces the literal value you want is called literal syntax. Different types have different literal syntax rules. We’ll talk about these rules, as well.

There’s a lot to cover. In this post, we’ll explore the specifics of text (strings). If we explored all of the types in this post, it would get mighty long, so we’ll save the others for later.

Continue reading →

Automating Column Name Renames

• , , ,

Ever rename a table’s column names to transform their style from what makes sense in the database world to what looks attractive in a reporting environment?

Perhaps a data source returns field names like:

user_id, username, first_name, last_name, is_active_user

In your report, you’d like these columns to show up as:

User ID, Username, First Name, Last Name, Is Active User

The obvious way to achieve this is to use the query editor to manually rename each column. For five columns in one table (such as the above example), hand-transforming their names isn’t too tedious. However, imagine a report that uses ten or twenty tables, each with twenty or thirty columns—and it’s a different story.
Continue reading →

SQL for C# Developers

• , ,

You’re a developer. You know how to write code that interacts with databases. Using tools like Entity Framework and LINQ, you can push and pull data to and from data sources. But how comfortable are you directly talking to a relational database in its native dialect—SQL? Maybe you can cobble together a basic SELECT statement—but when you do, do you feel like a traveler in a strange land where you barely know the language?


Continue reading →

Power Query M Primer (Part 5): Paradigm

• , , , ,

To improve our understanding of how Power Query works, let’s step back from the pieces and look at the paradigm—the methodology that M is built around. Conceptually, how does M work? How does it think? What makes it tick? The particulars, like variables, let expressions, functions and identifiers, are all important, but if we only focus on details, we might miss the big picture. We might not see the forest because we’re looking so intently at the trees. Let’s step back and learn about M’s modus operandi.

Without an awareness of M’s methodology, we might spend our days writing M code perplexed by certain behaviors, puzzled by why M seemingly doesn’t let us do certain things we might want to do and perspiring as we waste effort trying to mimic code patterns from other languages that are needless in M.

Continue reading →

Understanding Cancellation Callbacks

• , , , ,

In the ideal world, all .Net asynchronous methods support cancellation tokens: When invoking a method, simply pass it a cancellation token. Then, at the appropriate time, cancel the token and the asynchronous operation terminates.

Alas! We don’t live in the ideal world. Not every method we might asynchronously invoke works with cancellation tokens. When faced with an asynchronous operation we want to be able to cancel that doesn’t support cancellation tokens, one option is to implement our own cancellation logic by registering a callback with the token. When the token is cancelled, callbacks registered with it are executed.

Introducing callback into the picture raises questions around if/when, where and how those callbacks are executed. For example:

  • Will the callback be invoked via the synchronization context that was current when it was registered?
  • If multiple callbacks are registered, are they run synchronously or in parallel?
  • If a callback raises an exception, can that exception be caught?

In the case of .Net’s Task Parallel Library and its CancellationToken and CancellationTokenSource, the answers to these questions revolve around when cancellation occurs and how it is triggered.
Continue reading →

Power Query M Primer (Part 4): Variables & Identifiers

• , , , ,

Up until now, we’ve used variables without talking much about their specifics. Since we’re operating under the assumption that you have at least a little experience with another programming or scripting language, how we’ve used variables has probably made sense.

However, there might be differences—perhaps even significant differences—between how variables work in other languages you’ve used with and how they behave in the Power Query M language. Let’s spend some time exploring variables and related concepts to clear up any lurking confusion and position you to take full advantage of what M offers.

We’ll start with a brief recap of the main unit where we define variables: the let expression. Then, we’ll talk about how variables (and other things) are identified. Related to identifiers is scope, so we’ll cover that, too. Next time, we’ll expand  our understanding of how variables work by learning about M’s paradigm.
Continue reading →

Power Query M Primer (part 3):
Functions: Function Values, Passing, Returning, Defining Inline, Recursion

• , , , ,

Not only can you define and invoke functions (as we covered in part 2), you can also pass them around. The ability to pass a function around without invoking it gives lots of flexibility.

Sounds complex? Yes, in words, but not necessarily in practice. If you’ve touched the Power Query M language, you’ve probably already passed functions around—just perhaps without realizing about it.

Then there’s the mysterious each. It shows up a lot in code generated by the query editor. What does it actually mean or do? Turns out, it’s a handy shortcut that can simplify code you write.

We have ground to cover. Let’s get going!
Continue reading →

Power Query M Primer (part 2):
Functions: Defining

• , , , ,

If you read part 1 in this series, you may have picked up a theme about expressions that produce values: A simple statement, like 1, is an expression that produces a value. let is also an expression that produces a value.

Guess what? A function is an expression that ultimately produces a value. Unlike the expressions we looked at last time, a function only produces this value when it’s invoked.

Usually, parameters are passed to a function when it’s invoked. The function can reference these inputs as it computes what it will return. Continue reading →

Lightning Talk: SELECT Doesn’t Always Return the Expected Number of Columns

• , ,

Last night, I had the privilege of sharing a lightning talk at the OKC SQL Server User Group (OKCSQL). This 17-minute presentation touches on four scenarios where the way columns are returned by SELECT might not match what you expect.

When you write SELECT statements, you probably have expectations like:

  • SELECT * returns all columns in the referenced object(s).
  • Each column is returned in a separate column.
  • Each column returned in visible in the result set.

However—at least in the world of Microsoft SQL Server/Transact SQL (T-SQL)—these assumptions aren’t always true.


Continue reading →

Power Query M Primer (part 1):
Introduction, Simple Expressions & let

• , , ,

Sooner or later, you may find yourself working directly with the Power Query M formula language. Tools like Microsoft Excel’s Get & Transform Data and Microsoft Power BI provide point-and-click interfaces allowing you to build data transformation sequences which behind-the-scenes are implemented in Power Query M. However, these query editors can’t do everything you might need. Sometimes direct editing and authoring of M is required.

Search the Internet and you’ll find many examples showing how to use this language to solve one problem or another but little is out there describing the syntax and paradigm of the language itself. A limited knowledge of these details may be insignificant when simply copying and pasting samples and editing variable and column names. Move beyond this to weaving and writing your own solutions directly in Power Query M and a solid understanding of the language’s syntax, rules and capabilities becomes most helpful.
Continue reading →