Archive for PowerQuery

Cross Join with Power Query

Power Query is still very new, so there is a lot to learn.  I like to mix my learning up by doing some formal learning (such as reading a book like M is for Data Monkey) as well as “on the job” learning.  What I mean by “on the job” learning is when I go out of my way to use the software I am trying to learn in small, manageable but meaningful new ways.  You need to take the opportunity to solve real world problems in your real job if you are to ever build your skills.

I had a situation this week where I need to create a budget table in Power Pivot.  I had a choice of either loading a weekly budget as a single record per store and using DAX to calculate the Year To Date budgets, or load a set of records (one for each week) in a budget table.  I chose the latter in this case as it gave me a chance to try out some new skills in Power Query.  I have recreated the scenario below.

Weekly Budgets by Store

I have a spreadsheet that contains the budget per store per week.  The budget is the same for each week for each store for the next 15 weeks.  The budget table looks like this – easy to enter the data but not optimal for Power Pivot.  In my real life example, there were many more columns for Shrinkage, GP and some other metrics across the page.

image

Long narrow tables are normally better for Power Pivot, so I used Power Query to reshape this table so it looks like the one below – using a simple un-pivot columns transformation from Power Query.

Read More

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

Power Query Combine Multiple Files in Folder – another method

I have blogged before about how to combine multiple files in a folder using Power Query.  The way I have always done it is to use a Function, and this is not hard to do if you follow the instructions I provided on my last blog on this topic at PowerPivotPro.com.

Recently I had a specific requirement where I needed to do a quick validation check over some CSV files to make sure that no data was missing.  I could have spent 2 minutes writing a quick function and then import all the files but it occurred to me that there is another way.

Why is combining multiple files hard at all?

Power Query has an inbuilt feature that allows you to import data from a folder (shown below), so why is this a problem at all?

image

Well there are 2 reasons.

  1. Firstly it is common for the files you want to combine to have a header row in each file.  If it wasn’t for this header row then you could just combine the contents of a folder into a single import and you would be done (as long as the files are all the same shape).
  2. The second issue is that Power Query only loads a few thousand rows of data into the preview window.  So sometimes you can’t even see the next header row to filter it out.  All looks fine when you write the query but then it fails on refresh.

So what to do? – well there are a couple of tricks you can use

I have created some sample data – I have 3 csv files all of the same format all the same folder.  Each of these sample files has 100,000 rows of data – sales for 1 day in each file.  You can download the sample files here if you want to use them to work through this yourself. Read More

Creating a Desensitised Copy of a Power Pivot Workbook

I have been working with a client here in Sydney were we are producing some Human Resource (HR) reporting tools to analyse what is happening across the business.  HR data is quite often very sensitive because it can contain personal information as well as salary information.  A lot of the calculations that are in the workbook we have been building do not require all this sensitive information to work – eg Total New Employees, Total Employees leaving the business etc.  The reports are going to work if the sensitive data is in or out, but some people are allowed to have the sensitive data and others are not.  So….what to do?!

There are a few approaches you can take to this problem.  The most obvious is to create a second copy of the workbook and remove the sensitive data.  But the problem that will then occur is you have 2 versions that need to be maintained over time.  As you build out new reports or make changes to your DAX, you have to do it in 2 places.  So that is a bit of a pain.  Power Query to the rescue!

Use Power Query to Create a Second Version

The solution I came up with is to use a couple of tricks in Power Query to create an easily repeatable process of quickly creating a second version from scratch when ever required.  So in short there is 1 version of the workbook, but you can quickly create a second ‘desensitised’ version in less than a minute.  This reduces the rework that you would otherwise need to do if you had 2 completely separate copies.  Here is the process.

Create the workbook that contains all of the data you need.

Use Power Query to shape the data import the way you want it.  I have created a simple table below to demonstrate the point.

image

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

Power Pivot Calendar Tables

Level: Beginners

Technically you don’t need a calendar table to use Power Pivot, however in all but the most basic use cases you really should use one.  Reasons to use a Power Pivot calendar table include:

  • A calendar table allows you to use the filtering power of Power Pivot to make your reports fast and snappy.
  • It allows you to filter your reports on attributes such as Year, Month, Quarter and any other aggregation of time you like.
  • It allows you to create your own customer view of time that may be different to a standard calendar (such as a 4/4/5 calendar) and/or create your own definition of what is a financial year.

And indeed in some situations you simply must have a calendar table – specifically if you want to use the inbuilt time intelligence functions in Power Pivot (like year to date measures, same period prior year etc). Now there are other ways to create formulae that calculate time intelligence results other than the inbuilt functions, but generally the DAX is a bit more complex.  More on that later.

Rules for a Calendar Table

Here are the mandatory rules of a date table if you want to use time intelligence functions.  You don’t have to follow these rules if you don’t need/want to use the in built time intelligence functions, but it is still good practice particularly if your data is at a “day level” of granularity.

  • You must have a date column in your calendar table
  • The dates in this column must be in a contiguous range that covers the entire period of your data:
    • no missing dates.  It doesn’t matter if you don’t work weekends, you MUST include ALL DATES in the calendar table including weekends.
    • no duplicate dates
  • The calendar table must be marked as “date table” specifying the date column when asked in the dialogue box if you are using a non-date column. (This step is not required in Power BI as you can’t use a non-date key, only Excel).image
  • The date calendar reportedly needs to run through to the end of the last year used in the data model (although in my experience this makes no difference).

Note: It is not mandatory for your calendar table to be at the “day” level of granularity  it could be week, month, hour, minute or what every else you want  but you can’t use the inbuilt time intelligence functions unless it is at the “day” level.

Additional Features of a Good Calendar Table

Read More

Start a Second Instance of Excel

Excel is a bit like a drug for me – the more I use Excel, the more I need to use Excel.  I work on a lot of Power Pivot workbooks these days and sometimes these workbooks need to run a refresh or recalc that can take a bit of time.  Increasingly I am finding that I am just sitting there waiting for 1-2 mins but instead I would like to be doing something else in Excel, even if it is just updating my time sheet or expenses etc.

I found this solution I am posting about here while I was working on my Power Query Documentation Excel Workbook (read about that here). Power Query is a “modal” window, and hence you can’t do anything else in Excel while a Power Query Workbook is open.  I needed a solution so I could do 2 things in Excel at the same time.

The solution to both of these problems is to simply start a second instance of Excel in parallel.  There are different ways to do this in each version of Windows.  If you are running Windows 8 like I am, then this is what you need to do.

The Adhoc Solution

  1. Click the Windows button on the Task Bar.
  2. Type “run” and hit enter
  3. In the run dialog box, type “excel /x” and press enter

image

This will start a second instance of Excel on your PC.  If you have a personal.xlsb workbook for your VBA code, you will get the following message.

image

You can click any of the buttons to dismiss this window and they all do slightly different things.  For the purpose of running a second instance, any of these options will work fine.

Create a Shortcut

Read More

Excel is the Swiss Army Knife of Business Intelligence

I was recently asked by an Industry Excel expert what made Excel such a compelling product for Business Intelligence, Reporting and Analysis.  As many people know, I have worked in IT as well as commercial business roles over the years, so I have seen ‘both sides’ of the story and hence I am qualified to understand the things that can go wrong between these departments.

Big complex software projects (like an SAP implementation) necessarily require specialist IT staff to work with commercial business staff to develop people, process and technology solutions that will solve business problems.  These projects are highly complex, expensive, time consuming and risky by their very nature.  It is not unusual for such a project to last 3-5 years and cost 10s of millions of dollars – more in very large organisations.  Traditional BI projects are a bit like this.  They are complex, expensive, and often fail to deliver on the user expectations.

Why Big BI Projects are so hard

When business people work with specialist IT people, there is an enormous communication overhead between them. I have seen plenty of great people in IT and in commercial roles, and they almost always want the same thing – a great outcome for the business.  But the problem is that people are different; especially IT and commercial people are often very different.   There is a very good reason why some people are successful working in Sales and other people are successful working as a programmer.  The skill set between these people are fundamentally different.  So it is inevitable that when these people work together on a project, there will be misunderstandings, mistakes and rework.  All of this comes at a cost of time and money, and in the fast paced business world some things are just not viable if they have to go through this cycle.  Unfortunately in traditional BI projects, there is no choice but for IT people to work for commercial people, because the technical side of the projects are so complex that there is no other way.

Often it is not worth the cost

Business people will always have things that are a priority to them but are subordinate to the greater needs of the business/others/competing departments etc. So when a business person says  “I need ABC report to analyse this business opportunity by no later than XYZ”, sometimes/often the timeline simply can’t be met or the cost of doing so is prohibitive.

In a world of competing business priorities, successful business people find ways to get the important things done – they don’t sit around discussing why things could not be done due to lack of resources, competing priorities etc.

Enter Excel – the Swiss Army Knife of Business

I think about Excel as being the Swiss Army Knife of business.   Excel is one of those universal tools that allows any business person to get on with important priorities without having to call in IT to do the work.  In fact in my experience IT people know very little about Excel – the skill is all with the business users.

modern excel sml

Excel can be learnt and used by almost anyone for any purpose, and often the cost/benefit ratio is completely unsurpassed by any alternative (eg long slow expensive IT lead BI project) to the point where it is not worth taking your Excel built solution to the next level. In a world of rapidly changing business challenges, it is the speed to value that counts.  If you can’t turn around the analysis quickly and affordably, then the opportunity will most likely simply pass you by.

And then there is Modern Excel

Rob Collie uses the term “Modern Excel” to describe the new features that Excel has delivered over the last 5 or so years.  These new capabilities include Power Pivot, Power Query, but also the evolution of Pivot Tables, Slicers and into the future will include the still developing tools like Power Map, Power View etc. There is no other BI tool that can deliver a speed to value ratio that Modern Excel can deliver.

If you want to take advantage of Modern Excel, you will definitely need to learn some new skills such as how to write DAX, or you may have to bring in some people for a short period of time to break the back of the work to get you going, but these are small one off investments that have immediate payback in a rapidly changing business environment. Once you have these new skills dialled up, you will deliver speed to value like never before.

Create Incremental Backups when using Power Query

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here This backup tip is still useful though.
This is a follow up to this post where I talked about Power Query and warned to take frequent backups when editing your Power Query Workbooks.  The issue is that Power Query is a bit flaky, and hence it is well advised to create incremental workbook backups with different file names in case bad things happen.

Anthony Newell kindly posted an idea (and some code) that he used to automate the process of taking incremental backups.  I took his code as inspiration, and then made quite some changes to meet my needs.  I am sharing this new code here for those that want to use it.  It is equally as useful for any workbook that you have invested time in, and you can’t afford to lose.  It doesn’t have to be a Power Query workbook.

What the Code Does

  • First it checks if there is a “version sheet” in the active workbook.  If not, it creates this version sheet.  The version sheet contains the current version number and the location of your backups.  You can manually change the default backup location in this sheet (but it always saves this as a sub folder of the original file location).
  • It then saves the active workbook with its current name in the current active folder (no version number added yet).  I have done it this way because I don’t want my active workbook file name changing all the time.  What I want is a backup that I can recover later if needed.
  • It then saves a copy of the active workbook in the archive folder with the current version number appended on the end.
  • It then closes the version with the version number added and reopens the original file so that you are working on the original file without the version number.
  • Finally it increments the version number on the version page.

Read More

Power Query Performance Improvements – Test Drive

Microsoft has just released the March ‘15 update to Power Query.  On the official blog, MS called out 3 things:

  1. Performance improvements
  2. Microsoft Dynamics CRM Online connector
  3. New transformations

Performance improvements?  You have my attention.  I have found that Power Query (and Power Pivot for that matter) are both slow to load Excel files.  The comparison in speed between a CSV and XLSX of the same data is significant (XLSX is much slower). It seems to me that Power Query wants to “read” the whole file before it starts importing, but with CSV is just starts at the top and gets on with it – well that’s the way I rationalise it anyway.   So I decided to test out the claimed performance improvements in the new release and post the results here.

My Test Data Consolidates 44 Separate Excel Workbooks

I wrote a Power Pivot dashboard and reporting tool for a customer that allows them to analyse key business metrics using time series trend reports etc.  The source data for this report is a standardised stand alone weekly financial report that is produced in Excel.  So in any 1 week, you get a snapshot of what happened that week – which is great – but it doesn’t help you if you want to compare weeks or trends over time. Read More

Use Power Query to Create Power Query Documentation

I wrote a blog post on PowerPivotPro.com last week on how to create a standard Power Pivot calendar using Power Query – from scratch.  I was doing some research this morning on how I could extend this concept to create a 4/4/5 Calendar (which is often used in the Retail Industry).  I was looking at this on-line reference documentation to find a function for a particular problem, and it was really hard to find what I was looking for.  There is no index at the top of the page, just a very long page of sections and formulae.

Then I had a brilliant thought – I wonder if I could use Power Query to download the Power Query documentation?  I tried it out, and the answer is a resounding yes.  This is what I did.

I copied the link to the web page.  This is the one I was using

https://support.office.com/en-us/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819?CorrelationId=81f57c48-1280-44ee-84ef-c6aa835405a9&ui=en-US&rs=en-US&ad=US

I then opened a blank Excel workbook and went to the Power Query tab and inserted the above link in the box.

image

The Power Query Navigator returned a long list of formula categories, so I knew I was onto something.

Read More

How to Correctly Install Power Pivot for Excel 2010

As most subscribers of my blog would already know, I am a professional trainer of Power Pivot here in Australia. I do a lot of work for Corporations at their own premises with their own PC equipment. Not every company has made the move to Excel 2013, and in fact I sometimes find companies have to move from Excel 2007 to 2010 just to prepare for this training. There are a couple of traps when installing the Power Pivot for Excel 2010 Plug-in (and the Power Query Plug-in for that matter) and hence I am blogging these instructions to help people and companies get it right the first time. The main trap is getting the wrong version. But first a comment about 32 bit vs 64 bit.

32 bit vs 64 bit Power Pivot Versions

64 bit computers have been around for many years. The 32 bit architecture is quite old now and 64 bit is a lot more powerful. There are actually 4 technical layers that can be configured with either 32 bit or 64 bit, but each layer depends on the previous layer.

Let me explain. Starting at the bottom and working up, if you ever install 32 bit, then you can’t install 64 bit at a higher layer. But the reverse is not true. You can normally install 32 bit on top of a 64 bit layer (except for – the plug-in itself).

4 layers

Let’s discuss each layer

Read More

Power Query Issues with Power Pivot

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here

PhilC at PowerPivotForum.com.au asked me for advice on the best way to use Power Query with Power Pivot.  If you have come to this post via Google, it is probably because you have had issues and are looking for answers.  If you are reading this post for any other reason, then hopefully it will save you some pain by following my advice on how to minimise these Power Query issues with Power Pivot.

Quickly – what is Power Query

Power Query is the best thing to happen to Excel since Power Pivot!  Basically it is a lite extract, transform and load (ETL) tool for Excel users.  It allows us Excel users to grab data from almost anywhere, shape it into a form that can be used in Excel (increasingly Power Pivot), load it into your workbook and then away we go.

So what’s the problem?  Let me explain

Here is the scenario.  You grab some data using Power Query and then load it into Power Pivot.  You start building out your measures and adding value to your Power Pivot data model.  After investing many hours, you realise that you really need 1 more column of data from your Power Query table.  You go back to Power Query, add the new column of data, save the workbook and “BAM” you get the following error message.

We couldn’t refresh the table YourTableName from connection YourPQConnection. Here’s the error message we got: OLE DB or ODBC error: The query YourPowerQuery or one of its inputs was modified in Power Query after this connection was added.

error

When you first see this message, you can feel your stomach sink through the floor.  You have put hours into your data model and how you have to “start again”.

How to fix this problem after it happens

Read More

2015 Australian Training Dates – Power Pivot for Excel

I have now locked all the training dates for my Power Pivot for Excel Australian Training public classes.  You can find full details of the dates here. http://exceleratorbi.com.au/powerpivot-training-australia/

live training Australia

Why you should attend my Power Pivot for Excel Training

  • I am affiliated with Rob Collie from http://powerpivotpro.com in the USA and I deliver training material developed by Rob that is targeted specifically with the Excel user in mind.  Rob was the Program Manager at Microsoft that worked on the first version of Power Pivot.  His unique insights on how the tool was designed to work makes this training course more targeted than most.
  • I have a background working for Coca-Cola and have years of experience both using data and building data tools.  I know what users think they need, and I know how to structure data to give them exactly what they really need.

Why you should register now

  • There are discounts for early bird registration as well as for multiple registrations from one company at a single session.
  • Everyone is busy working on what is important as well as what is urgent.  Learning a new skill to use a tool like Power Pivot will make you more efficient (save you time) and more effective (get better business results).  It is not urgent, but it sure is important.

Block some time in your calendar now so that you make sure you get the benefits from this training and can start to Excelerate your business results.

For more details

A full course overview is available here.  http://exceleratorbi.com.au/powerpivot-training-australia/  You can then click through for your location, find a date that works for you and click through to register.

How to access PowerPivot on iPad

ipad_screen

PowerPivot on iPad – great experience!

One thing I find when working with my customers and meeting PowerPivot users is that a lot of people want to know how to access PowerPivot when they are mobile.  Most frequently people want to access PowerPivot reports on iPad, however in many cases just presenting data for PC users in an ‘easy to consume’ and ‘easy to control’ manner is also important.  Well there are 3 ways that you can set up to allow iPad (and PC users for that matter) to access PowerPivot reports from a web browser, and it doesn’t have to be difficult or expensive if you do it the right way.  The three options are:

  1. Microsoft Power BI in the Cloud
  2. An On Premise instance of SharePoint Enterprise Edition
  3. A Cloud based SharePoint Enterprise Service purchased as Software as a Service (SaaS).

I will cover each of these in turn leaving what I consider to be the best until last.

 

First of all, a definition of the term Power BI

Read More

WordPress Reporting using Power Query

I recently created a Book Shop on my WordPress website using WooCommerce.  The next logical thing I wanted to do was to get an extract of all my orders/invoices that have been sold through the shop into Excel.  I couldn’t find a way to do this in the WordPress UI or via a free plug in, and then it occurred to me that maybe I could extract the data from the underlying WordPress Database using Power Query – and indeed you can.  This is how I did it.

Enable Remote Access to your Database

Log into cPanel for your website and go to “Remote mySQL”

image

Read More

Chris Webb’s Power Query Book Review

I have been waiting for this book Power Query for Power BI and Excel by Chris Webb for some time and I am pleased to advise it is a great book for a broad range of people with anything from zero to medium skills in Power Query. I fall into the later category and I found some value in every chapter, with increasing value per chapter as the book progressed.Power Query for Power BI and Excel

Chris has written this book in a thoughtful manner. He starts with the basics and then layers concepts one on one until he culminates with a chapter of real world examples of easy to understand use cases. These use cases leverage the skills learnt in the book and give you something meaningful to work on to absorb how the concepts are used in real life. I am confident that with these new skills I will be able to accelerate my self learning using the book as a reference along with the other resources provided by Chris as links in the book.

Chapter 1 is a good overview for people that are new to Power Query. Anyone that has used the tool a bit won’t find much here. There is however a good section on licensing that clearly describes what products come with each licensing purchase.

Chapter 2 takes you through each data source format one at a time, and gives examples of each. You could do this yourself with a bit of trial and error, but it is very convenient to have this as a reference. There is also an excellent reference about advance search functions to improve the quality of your searches over the web, and a great tip on referencing other Power Queries as a data source.

Chapter 3 is a comprehensive overview of the transform capabilities of Power Query. This would be very useful for newbies and a useful section to skim for those with a reasonable working knowledge to validate complete breadth of knowledge.

Chapter 4 focuses on what happens to the data when you return it to Excel and/or the data model including some tips on refreshing data.

Chapter 5: the M language! Chris writes this chapter to help you get the most out of these documents by explaining concepts not covered so well in the tech documents themselves, such as the Let statement.  You are not going to be an M Language wizard after reading this chapter, but you can expect to have a much better understanding of the structure of the language, and how to make sense of the output generated from the user interface.  With this base to build upon, you can learn more about M yourself much in the way that you can learn to program VBA from the Excel Macro Recorder.

Chapter 6 covers multiple tables. Building on what you have learnt about lists and the M language, the concepts of joining tables somehow doesn’t seem so hard now. I always struggled to get my head around the syntax before reading this chapter. The incremental build up to chapter 6 and the content of this chapter have sorted that out for me.

Chapter 7 extensively covers Power BI cloud integration, including sharing queries in the cloud, how to manage the quality of queries through certification, and some tips on how to manage version control. If you are using Power BI in your organisation, these will indeed be valuable information to get off on the right track.

Finally chapter 8 is the culmination of everything taught in the book. Chris gives step by step instructions on how to create specific transformations to data in real world use cases. He takes you through each step so that you learn what to do, then explains anything that needs further clarification. I would highly recommend actually doing each of the recipes on your own PC as a learning experience to cement your new knowledge.

All in all, a great purchase at under $20 for the eBook.

PowerPivot Forum Recruitment Drive

Back in early May I realised that there was not much in the way of a dedicated forum for PowerPivot users, especially for the general user.  There are some specialised forums out there for Microsoft Developers but not much for the average user.  As a result I went ahead and set up a new PowerPivot Forum and introduced it to the Web community.

PowerPivotForum.pngSo far so good – there are 30 members and growing slowly.  But any forum is only as good as its member community (size and participation level).  It doesn’t matter if you are a beginner or an experienced PowerPivot, Power Query, Power View or Power Map user – this is the place for you to come and ask questions or find answers.

If this is not you but you know someone that fits the bill, please share the link  with them.

http://powerpivotforum.com.au/

Deep Dive into Power Query M Language

Matt's curated best self serve BI content

In this session, Matt Masson and Tess Palmer do a great job of showing everyone the depth and power of the Power Query M language that is the backbone of Power Query – it makes my list. Clearly they didn’t expect too many people in the audience to be able to follow them along the way, which came out in a light sarcastic way at times, but I think this reflects the state of maturity of this tool. Not too many people have deep knowledge since it is so new. I am waiting for Chris Webb to release his new book so I can get in some late night reading. Here is the agenda – mainly demos which is good.

M language agenda

and here is the relevant content

Time Stamp Description
0:00 Intros
1:55 Demo/Overview of the Power Query product, tab etc. Not too much new here.
2:50 A good lesson about the SQL Statement window. Don’t use it.
3:15 A great 2 min overview of what Power Query does, how the formula bar works, how to access the advanced editor, and good advice to use the UI first, then edit formulae.
5:00 Intro to M Language, why Microsoft selected it, why DAX is not up to the job.
7:30 Introduction to the concept of “Query Folding” – how Power Query pushes the work back to the database when it can. Good to know.
12:15 Some information about Library Functions. Here is a link with a PDF download that has a formula library reference that they talk to on this slide. http://go.microsoft.com/fwlink/?LinkID=320634
13:30 Demo time.

  • Explains the new features to change the default load behaviour to Excel and/or data model.
  • How to get inline help for the M language
  • Shows making changes to the advanced editor to bring in a 3 way join of tables from a DB.
17:45 Language Flow explanation – how the language passes on transformations from one step to another.
19:45 Type System. Explains the different data types, and how to define them, with a demo
29:30 Demo: Import and combine multiple CSV files into one table
32:40 Demo: Import a single file (Excel), edit the query to create a function, then import multiple versions of the same file and combine them. Matt shows you how to edit the regular query to a function.
41:30 Demo: Download complex data from Web. Good example of the breadth of the language using functions to download data.
48:15 Demo: Another web demo downloading multiple pages using a function.
55:40 An example to extract data from Binary objects. Far too deep for me!
61:00 Tips and Tricks. Worth watching. #shared in the formula bar will give you a list of functions and keywords.
64:10 Links Page
Language Reference: http://go.microsoft.com/fwlink/?LindID=235475
Library Referenece: http://go.microsoft.com/fwlink/?LindID=320634
Office Help: http://go.microsoft.com/fwlink/?LindID=398594

Here is the full Power Query M Language Video

And here is the link to all the BI Stream Content

A smorgasbord of Microsoft Self Serve BI at TechEd 2014

Matt's curated best self serve BI content

Last week in Houston Texas, Microsoft held TechEd 2014 (North America, 12-15 May). I was browsing the videos from this conference and wanted to share the wealth of material that is available for anyone to view. If you just filter on the “Data Platform and Business Intelligence stream“, there are over 40 sessions focussed on everything from Power Query, PowerPivot, SQL Server and more.

One of the things I really love about my new career as a freelance Self Service BI expert is that I can consume content like this, and actually get value that helps me be successful in my job. I plan to watch a lot of these videos over the coming weeks, and I will share back here a brief summary of those that I find most useful.

What’s New in Power Query for Excel

Let me start with this beauty of a session. It runs for 1 hour 15 mins and it gives an excellent overview of why Microsoft has decided to build Power Query, as well as a number of excellent demos of the power of this great new tool. Towards the end of the video (at the 45 min mark) they start to show some of the new features that will be released in the near future. If you even wanted to answer the question “What is Power Query and why should I care?”, then this is the video for you.

Here are the highlight points in case you don’t want to watch it all, with the video below.

Video Topics
Time Description
0:00 Intros
2:20 Why they have built Power Query
11:15 Demo – pull data from web search transform and produce a Power Map chart
17:50 Demo – pull data from web URL transform and produce a Power View chart
23:20 Demo – data mashup pulling data from different data sources combining and producing a Power View time series chart
32:10 Demo – multi dimensional data demo (Business Objects Universe)
40:30 Demo – pull data from web page URL where data is spread over multiple pages. Uses advanced Power Query technique using a function written in the "M" language.
45:25 The start of all new stuff not currently released.
47:30 Demo – demo of new tool to "transpose" data direct in Power Query
49:30 Demo – demo connecting to Microsoft Exchange to analyse emails over time (very interesting) – already available.
60:20 Demo – recent data sources feature that keeps a list of previous data sources
64:00 Q&A

Here is the link to all the videos: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2014#fbid=