Archive for Uncategorized

PQL – Pronounced Peequel

Back in February 2016, I was tweeting with some others about the need for a new name for the Power Query Formula Language.  I came up with the name PQL, pronounced Peequel and made a suggestion to this effect at ideas.powerbi.com.  This post today is to explain why a name change is required, and to seek support for renaming the language to PQL.

A Background on the Current Name(s)

The official name for the language used in Power Query is the Power Query Formula Language.    The “unofficial” name is “M”.  The reason this is the unofficial name is because the Power Query Formula Language was derived and built from a language called “M” – so this name stuck.

The Problems With These Two Names

There are two problems with the name Power Query Formula LanguageThis name is a mouthful and hence hard to say quickly, and secondly it doesn’t abbreviate well (PQFL is not a great abbreviation and certainly not pronounceable).

The trouble with “M” as a language name is exactly the opposite of the Power Query Formula Language.  M abbreviates too well and is far to short for common use. 

example:

Matt: “Do you know how to write any M?”
Other Person: “What is eniem?”

Even worse, when you try to do a search on the Internet for “M”, it is very hard to find what you need because you simply get everything back.

Enter PQL as a Better Name

My suggestion of PQL, which is short for Power Query Language of course, is (I think) a great replacement name.  The reasons this name works include:

  • It is a TLA – you can’t have too many TLAs!
  • It is pronounceable (Peequel).  A good TLA is pronounceable!
  • It sounds like SQL (Seequel), which is the name of the universal database Structured Query Language.  In a way SQL is a cousin of PQL and hence users will just “get it”.
  • It will be easy to search on Google (even though it is used already for some other languages). It is certainly no worse than DAX – Data Analysis Expressions DAX which is also used by the German Stock Exchange.

Please Vote for This Idea

If you agree with my logic and the need for a new name, please vote for this idea at ideas.powerbi.com.  Please make sure you leave a comment in the comment section stating that you think PQL is the best name, as the idea doesn’t specify what the new name should be.

Convert a Cell Value into a Column with Power Query

Level: Advanced Power Query

I am in the process of finalising some action items after a great Power BI, Power Pivot and Power Query training course in Melbourne.  During the training we looked at a scenario for one of the attendees where they needed to combine data from multiple Excel files into 1 big table.  I have blogged about this before in 3 articles starting here.

In this specific scenario we looked at in the training, there is a weekly extract of data from another BI system (sound familiar?).  Each week someone must use “Export to Excel” to grab the current week’s data and dump it into a file (a typical action for a BI system that isn’t user friendly).   The file format of the export looks something like this (indicative).

image

The key points about this data structure are.

  1. There is a date in a cell in the file header.
  2. The details are also in the same file, but there is no date column in the detail section.

The requirement is to end up with a table like this.

image

The rest of this article describes how to do it.

Step 1.  Connect to the file using Power Query

image

Step 2.  Filter the Table so the Date Row is at the Top

To do this, I simply did a “Remove Top Rows” to remove the top 2 rows.

image

Step 3.  Killer Trick to Extract the Actual Date

The next step is a killer trick.  The idea is to extract the date from the first column in the top row into a scalar value so it can be used later.  To execute this step, you need to use a hand written PQL language formula.

Click on the Fx button to insert a new custom step

image

If you can’t see the Fx button, it means you don’t have the formula bar turned on.  Turn it on like this

image

After clicking Fx, a new custom step will be added to the query pane (1 below).  The step (by default) assumes that you want to use the previous step as a starting point (2 below).

image

To extract the date as a scalar value (2 below), you need to use the function Record.Field as shown in 1 below.

image

Note how I have wrapped the original step #”Removed Top Rows” inside the function Record.Field.  The {0} portion of the formula says “take the first row from the previous step, and the “Column1” part says take the data in the column called “Column1”.  The intersection between the first row and this column is a single cell, and hence this formula is accessing the actual value (scalar value) in this one single cell in the workbook.  You can see it is a scalar value (2 above) because it doesn’t show a table, but a single value.

Strictly speaking I didn’t need to force the date into the first row – I just did this because it was cleaner.  I could have just as easily reference the row/column without removing the top rows with this formula

= Record.Field(#”Removed Top Rows”{2},”Column1″)

The number inside the curly braces represents the row number, with a base 0 index.  So 0 is the first row, 1 is the second row etc.

Step 3. Second Killer Trick – Get the Data from an Earlier Step

The next (little known) trick is that I can actually go back and get the data from an earlier step.  Power Query by default always uses the previous step as the starting point for the next step – a logical flow.  But it doesn’t have to be that way.  In this case the previous step is simply the scalar value = date, and that is not what I want. I need the step previous to that which is “Removed Top Rows”. 

Click on the Fx button to create another new custom step.

image

Now change the formula in the formula bar (1 above) so it points to the step “Removed Top Rows” instead of pointing to the step “Custom1”.  Like this

image

So now I am back were I was before extracting the Scalar Date value.

Step 4.  Proceed to shape the data the way you want it

I then executed some steps to cleanse and shape the table the way I want the data.  I ended up with the following.

image

Step 5.  Add the Date as a Column

The final step is to bring the date from step 2 into a column in the table.  This is quite easy now.

Add a Custom Column, and refer to the step “Custom1” that returned the scalar value

image

 

And I end up with this

image

Exactly the type of data structure needed to load into Power Pivot.  I simply reordered the columns to put the date column first, and then it was done.

Final Comments

If you want to (or need to), you can then turn the above query into a function.  Read more about that here.  If you turn it into a function, you can then use it to process all files in a folder and combine them into a single data table to load to Power Pivot.

If you want to look at the workbook, you can download it here => Extract scalar date

M is for Data Monkey Book Review

M is for Data MonkeyThe long awaited book from Ken Puls and Miguel Escobar has just been released in digital edition.  I read this book over the weekend and wanted to share my experience with anyone considering buying this book or wanting to learn more about Power Query.

This book is 220+ pages of Power Query information that covers the length and breadth of what you need to know to use this great Excel tool from Microsoft. Ken and Miguel have structured the chapters in a way that builds from the simple introductory topics up to some of the more advanced (but still easily learnable) techniques that can only be accessed when you get into the M programming language (also called the Power Query Formula Language).

Read More

Tidy Up Power Query

I have been doing a lot of work with Power Query for clients in recent months. It is not always obvious exactly what you need to do when you start writing a Power Query. As a result I have found that the Power Query workbooks tend to “evolve” during the process, and they can sometimes be a bit messy in the end. This had me thinking about how to tidy things up a bit before finishing. There are 2 tips that I have.

1. Remove all but the last Reorder Step

In the example below, I have loaded the customer table from Adventure Works. During the process of creating the query I changed the order of the columns on a couple of occasions (shown below).

Read More

Excel Keeps Crashing? Check your VBA code

I use Excel 2013 64 bit and sometimes Excel just keeps crashing on me.  It used to happen to me a lot more in the past, but after I discovered what was causing the repeat behaviour I have been able to greatly reduce how often this occurs.

First a description of the problem

excel has stopped working2If this happens to you then you will already be aware of this scenario.  You are inside your workbook doing something important (it always happens when you are doing something important), and then the screen background greys out, and you get a message saying Microsoft Excel has stopped working.  It then “pretends” to be looking for a solution prior to telling you that it can’t fix it and you have to close the program.

And if your experience is anything like mine, once this starts to happen with a workbook, it keeps on happening over and over with seemingly no way out.

So what causes this repeat behaviour?

Well there are 2 main causes for this problem.

Read More

Solved: Power Query Issues with Power Pivot – Exception from HRESULT: 0x80020009

level: Intermediate

I have written a couple of posts here and here about the problems and dangers of using Power Query with Power Pivot.  Well I am very pleased to say that I now fully understand the problem, how it is caused and how it can be avoided.

Edit: Nov ’15.  Microsoft has released a fix to prevent this problem occurring.

First a Clear Statement of the Problem

You use Power Query to create a table and load it into Power Pivot. You only load a subset of columns to start with.

initialdata

All is fine for a while and you create a whole lot of DAX formulae – life is sweet.  You add new rows to your Power Query Table and refresh the data into Power Pivot – not a care in the world.

refreshdata

Then one day you need to make a change in Power Query (eg to bring in another column of data that you left out originally).  You go into Power Query and make a change to your table to bring in a new column.

powerquery change

You save, close and load and then BAM – you get the following dreaded error message.  You didn’t even see it coming before it hit you right between the eyes.

exception

Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))

The only option now is to remove the table from Power Pivot and then reload it.  When you do this you lose all your relationships, all your measures in the table, all your calculated columns you created in the table (not that you should have any of those – right?!)

L2WD banner ad

What causes this?

It is very easy to prevent this happening if you know what causes it.  This is one of the many high value things I learnt at the PASS BA Conference in San Jose in April 2015 (in 2 separate sessions – one by Chris Webb and one by Miguel Llopis) .  So what causes the problem?  The issue is caused when you make structural changes to the table in BOTH Power Query and Power Pivot. Read More