Power BI: M Query Basics (Part 2)

This is the second part of my series on Power BI M Query Basics. In Part 1, I defined M Query and talked about the structure of how an M Query query is written. If you haven't read the first part, go ahead and read it now....I'll wait.

OK, on to

M Query - the Second Part!

 

M Query Editor

Open up a Power BI report in Power BI desktop. On the Home ribbon, click on the Edit Queries > Edit Queries to open up the Query Editor.

In Query Editor, you can get to the M Query behind one of your queries by clicking on Advanced Editor either on the Home tab or on the View tab of the editor. (it's available in both locations)

Once you have Advanced Editor open, you can edit the M Query associated to the selected query.

The first thing that you will notice about the built-in M Query editor is that it doesn't have any context highlighting, matching brackets are not highlighted, and syntax errors are extremely difficult to track down. Editing M query in here is not for the faint of heart - most of us are very thankful that the Query Editor user interface can auto-generate about 90% of all of the M Query code that will be needed.

Thankfully, it's not the only option. 

M Extension for Notepad++

 

First up is the M Extension for Notepad++. I'm a big fan of Notepad++. It's lightweight, loads fast, and has context highlighting available for most programming languages. The official plug-in page for Notepad++ doesn't yet have a link for a fully completed extension, but you can visit Lars Schreiber's blog for instructions to set up your own M language highlighting (you only have to do it once, then it's there forever as a language choice in Notepad++).

The above screenshot shows the same "May Earthquakes" M Query except within Notepad++. Already you can see that pasting your M Query into Notepad++ makes it so much easier to understand and edit. 

M Extension for Visual Studio Code

 

A second editor to try for working with M Query is Visual Studio Code. This is a free application available from the Visual Studio Code site. Once you've installed Visual Studio Code, click on the Extensions button and perform a search for Power (full name is Power Query M Language).

Hit install and it will be ready for use. What's really lovely about Visual Studio Code is that the M Query Extension provides intellisense - when you start to type it pops up a context sensitive list of possible M Query functions which could apply to what you are typing.

This is handy for medium-weight development where you are typing in a substantial amount of code or making major changes and prefer to have some assistance with syntax. You will have to paste the code back in Power BI Advanced Editor to test it.

Power Query SDK for Visual Studio

 

For serious M Query writers, Visual Studio now has a Power Query SDK. This add-in for Visual Studio 2015 and Visual Studio 2017 allows developers to create Visual Studio projects using M Query (aka "Power Query). There are two types of projects available:

The Data Connector Project is intended for creating additional Power Query extensions, and the PQ file is for writing M Query queries like we've been working with.

Create a new PQ file project and paste in the M Query from your Power BI Advanced Editor. The PQ file projects can only handle one PQ file per project, so don't try to add a second PQ file. If the M Query contains a syntax error, Visual Studio highlights the error with the same red underlining as it does with any other programming language. It also will provide intellisense and bracket highlighting to make coding easier.

But wait...there's more! 

Visual studio also allows execution of the M query. To run click on the "Start" button (the usual debugging button). 

The first time you click on "Start", a dialog will pop up requesting credentials for the data source. Once credentials are set, close the Error dialog and click "Start" once more. The window will pop up again, but this time data should load. 

This will certainly save time since it won't be necessary to copy the M Query back into Power BI Advanced Editor until all modifications have been completed.

 That concludes Part Two of the M Query blog series. Join me in Part Three when I walk through a demonstration of writing M Query without using the Power BI Query Editor User Interface to auto-generate the M Query.

TSQL2SDAY 101: Essential SQL Server Tools

I've decided to jump onto the bandwagon and submit and article for this T-SQL Tuesday. This month, Jens Vestergaard (b|t) is hosting, and he's asked us to post about "The Essential SQL Server Tools in my Stack". These are the tools in addition to SSMS and Visual Studio Data Tools which we use for every day SQL activities.

In case you're new to T-SQL Tuesday, it is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for a very long time - we're currently on #101.

LadyRuna's Essential SQL Server Tools

As a consultant, I am often in the situation where I cannot make changes to the system I am using, so I frequently have to work with whatever they provide. Sometimes I'm lucky and they provide a few of my favorite tools, and other times not so much. Therefore, the tools I'm listing are the ones most frequently available to me in my consulting gigs.

Notepad ++

I hear you laughing, but think about it: you're tasked to create an SSIS package to import a flat file into your SQL server. Before you do anything in SSIS, you have to look a the file so you know whether the package will require any data transformations prior to loading the content into the SQL table.

If you open the file in Excel, it may automatically change the data types incorrectly (a good example is changing an account number that begins with 00 to an integer. That action removes the leading zeros, essentially modifying the account number's value), or it may reformat values in an undesirable format (changing phone number to scientific notation). If you open it in Notepad++, you will see exactly what the source system output and you will be able to accurately map all of the data correctly the first time. 

Notepad++ also offers context highlighting for various programming languages, a spell-checker, and prompts for frequently typed words (the list seems to grow the more you use it). Notepad++ highlights pairs of parenthesis, brackets, and braces; toggles comments; gracefully handles indents; and has fantastic searching capabilities. I like it enough that I find I use Notepad++ more than SSMS for many coding activities.

Poor Man's T-SQL Formatter

This is a free open-source downloadable T-SQL formatter available for SSMS, Visual Studio, and Notepad++ . It provides enough formatting options to satisfy most of the T-SQL formatting standards I've seen. If you haven't tried it yet, please download it and see for yourself.

Poor Man's SQL Formatter in Notepad++

Poor SQL

This is a free, online SQL formatter. Since there is nothing to download or install, I use this to format my SQL when I'm working in an environment where I don't have access to any other formatter and I want my code to be formatted consistently and / or in compliance with the customer's style. I have not yet encountered a situation where this option was blocked.

Task Factory

Some of the Task Factory Components

Until I started working for Pragmatic Works, I'd never used Task Factory when developing SSIS packages, but now that I do, I've found it actually makes working with SSIS pleasant.

Task Factory has over 60 SSIS components which are optimized to run faster than the native SSIS components. The Task Factory components handle many tasks which previously could only be handled using custom script components - allowing users to avoid the nightmare of debugging and rewriting script components for different environments.

BI xPress

Yet another Pragmatic Works product that I wish I knew about years ago is BI xPress. It includes auditing and notification frameworks which can easily be applied to any SSIS package to provide consistent and efficient auditing of SSIS package runs.

Once the auditing framework has been applied to a package, the SSIS Monitoring shows details about each run - including the values of variables at each component and, if the package is still running, which step it is currently running. I've found it's great for debugging packages that "worked on my machine" but aren't working properly in the SSISDB. I can see everything about the package in the SSIS Monitor in a view similar to what I'd see when running the package in Visual Studio.

There are more features to BI xPress than that, but in my experience those features are the ones I go to most often.

DOC xPress

One more Pragmatic Works product I wish the companies I worked for years ago had is DOC xPress. Its main function is to create documentation of SQL Server, SSAS, SSIS, SSRS, Excel, Oracle, and other database-based systems. It does this by pulling metadata about each of the objects and compiling it into a single cohesive unit. The documentation can be output into CHM help file, HTML, RTF or Word Documents.

I've found this is a fantastic device for learning about a new database system because all of the information is available in one spot. If a change occurs on a database that has been documented, after taking a new snapshot, the "before" version of the database can be compared to the "current" version to see what has changed.

DOC xPress even has a "lineage" feature which allows users to answer the question "Where did this field originate?". This means you can potentially pick a field in a table in the SSAS cube, find the SSIS package that loaded the cube, and trace that field back to the source database where it originated. Tracing the "lineage" with this tool is many times more efficient than the old fashioned method of getting a pre-dawn phone call about a process failure because someone made a change and didn't find all of the downstream effects of that change.

Power BI: M Query Basics (Part 1)

When you work with Power BI, you'll find that there are two languages to work with: DAX and M. Since most of the M query that you will regularly use is automagically (*that's not a real word, but it SHOULD be one) written by the Power BI user interface, the majority of Power BI developers focus on becoming proficient in DAX and really only look at M when they're stuck in a situation that requires that they learn it.

This M&M is a mashup of chocolate
 and pretzels

So, what exactly is M?

Some people think M is for "Microsoft".

Not quite.

According to the Microsoft Power Query M Reference,  "The Power Query M formula language is optimized for building highly flexible data mashup queries. It's a functional, case sensitive language similar to F#."

Generally speaking, most Power BI professionals settle upon saying that M is for 'Mashup'.

Now, when I heard that, the first thing I thought of was the orange pretzel M&M guy - he's a mashup of a favorite chocolate candy with pretzel and maybe some other stuff. Quite enjoyable, but not your standard or traditional M&M - just like M Query.

M is a functional script language with a step by step language structure used for data preparation. With it, one can transform or combine data sets, pivot, unpivot, split columns,  add or remove columns, remove unwanted characters, modify data types and perform data manipulations, among other things.

In Power BI, M Query can be automagically generated within its Query Editor user interface. The "Applied Steps" listing there summarizes all of the sequential M Query steps that have been applied to the current query, allowing users who aren't proficient in M Query to quickly view the changes made to the data set and to modify the changes without intimate knowledge of the M Query syntax.

Let's take a look at the structure of an M Query. You can access the M Query code from the Query Editor in Power BI by clicking on "Advanced Editor".

Here is an example of a Power BI M Query:

In Dark Blue, we have the "let" expression. This is equivalent to our SQL "CREATE PROCEDURE" declaration - it demarks the beginning of the series of steps used to transform the data into the final table to be used in Power BI.

Next, in Purple, we have the "in" expression. The Named Expression which follows "in" is the Output or final result of our M query. This defines the values that are seen in the grid view within the query editor.

In Aqua, we have the Named Expressions and variables. Each Step is a Named Expression which functions as a label to identify that step. Steps that are a single word may be declared by simply using the word alone, however, step names which contain spaces must be marked by using # and then enclosing the words within ". An example would be #"Renamed Columns"... Note that Source, which is only one word, does not need either the # or the " surrounding it.

In Green, we have the M Functions. These are the transformations performed upon the data set and the commands used to shape or mashup the data as needed by the final Power BI report.

In Black are the references to the previous steps. If you check each line, you will see that the Previous Step reference (in black) exactly matches the Named Expression (in Aqua) in the line above it. This helps demonstrate the step by step nature of the language - each successive step references and builds upon prior steps.

 There are a few other types within the M Query syntax, such as strings in Brick Red, data types and key words, but generally speaking the above summarizes the M Query syntax.

SQL Saturday Phoenix - 17 March 2018

Just wanted to say that I'll be speaking at SQL Saturday Phoenix on 17 March 2018 on "Power BI: The Magic of M."  My presentation with be in the first set of sessions (10 AM), so be sure to arrive early! I'll be debuting my super special (yellow & black) "Power BI" dress, too.

The abstract is:

Power BI: The Magic of M

As the popularity of Power BI increases, knowledge of M, the language used in Power Query and queries in Power BI, is becoming more necessary. Although the Query Editor in Power BI generates most of the M needed for basic transformations, it's just the beginning of its capabilities. In this session we will look under the hood at the M query and demonstrate some of the magic of this powerful language.

SQL Saturday Phoenix will be held at Grand Canyon University, 3300 West Camelback Road, Phoenix, Arizona, 85017, United States

T-SQL Tuesday #99 - A Passion for Sewing

This post is part of T-SQL Tuesday, a monthly blog party on the second Tuesday of each month which was started back in 2009. This month's host is Aaron Bertrand (B|T), who presented us with a choice between two possible topics - something non-technical you're passionate about or t-SQL bad habits. I know I'm a week late, but I didn't see the topic notification until today, so I thought I'd create a post anyway because I needed to resurrect my blog and this post was a good excuse for reviving it.

Honor Harrington

My passion outside of the SQL server community
is sewing. Much of my earlier sewing was costumes for myself which I'd wear to Science Fiction, Anime, and Steampunk conventions. Over the years, I've entered - and won - numerous costuming awards at the conventions for my creations.

Katamari!

Sesshoumaru

I soon realized that the reason I preferred wearing costumes wasn't simply because they were unusual garments but because they fit much better than the clothing I could purchase at stores. This led me

Neo and Trinity

down the road of making nearly every garment I have in my closet. I've made some shirts for my husband, sewn curtains for the house, recovered the sofa cushions, made pillows, sewn purses and totes, and made quilts.

Pumpkin Scissors

SQL Cruise formal dress

I also collect antique and modern sewing machines. Currently I have 15 machines, including a 15-needle embroidery machine. Sewing is rather addictive - once you find out how easy it is, you can let your creativity fly and make anything you desire.

Redline 1501

Singer 201

Willcox & Gibbs