Archive - Blog Posts on Power Query

Who Needs Power Pivot, Power Query and Power BI Anyway? - Level: Beginners One of the great challenges Microsoft has faced with its “new” suite of Self Service BI tools (particularly Power Pivot) is that most people that could benefit from the holy trinity (Power Pivot, Power Query and Power BI) don’t even know these tools exist, let alone how the tools can help them succeed in […] Read More
Query Dependency View in Power BI Desktop - I have been on holidays taking a much needed break from work, so it has been very quite on my blog over the last few weeks.  But I am back now! I woke up this morning to a very pleasant surprise – Microsoft has delivered the long waited for Query Dependency View in Power BI […] Read More
Shaping vs Modelling in Power BI - Level: Beginners Power Pivot, Power Query and Power BI are 3 products that are closely related to each other and were all built for the same purpose – enabling Self Service Business Intelligence.  I first learnt to use Power Pivot for Excel, then Power Query for Excel, and finally Power BI.  But there is a […] Read More
Power Query to Combine Web Pages - Level: Intermediate There was an interesting question this week on http://powerpivotforum.com.au asking if there was a smarter way to user Power Query over multiple identical web pages to scrape the data in a single query.  I have been meaning to blog about this for a while, so it is a great opportunity for a mini-Friday […] Read More
Power Query Over a Command Screen Output File - Level: Intermediate I spent a lot of last week helping to configure Power BI in preparation for go live for a client.  One of the important things to do when designing a Power BI solution is to make sure you have a good design for your user security access.   Today I am going to share […] Read More
Self Referencing Tables in Power Query - I have had this idea in my head for over a year, and today was the day that I tested a few scenarios until I got a working solution. Let me start with a problem description and then my solution. Add Comments to a Bank Statement The problem I was trying to solve was when I […] Read More
Conditional Columns in Power BI Desktop - Level: Beginners Microsoft recently announced a new conditional column feature in Power Query (Power BI Desktop version) that warrants a blog post.  This feature makes the process of creating banded columns much easier for the average user.  I blogged about banded columns using DAX here, and now there is this second, potentially easier approach.  In […] Read More
What is Power BI - Level: Beginners What is Power BI? Microsoft Power BI expert, Matt Allington, explains This ‘What is Power BI’ post is the third in my “what is” series having previously blogged about “What is Power Pivot” and “What is Power Query”.  This topic is the hardest of the three to give a straight answer, however it […] Read More
What is Power Query - Level: Beginners I am still astounded by the number of people I meet that have never heard of Power Query and Power Pivot.  The good news is there are lots of people out there in for a pleasant surprise.  I think Power BI will solve the “lack of awareness of Power Query” problem over the […] Read More
What is the Best Training for Business Analysts? - Level: Beginners The rate of change in the tools available to a Business Analyst has never been faster than it is in 2016.  The market is changing so quickly that many Business Analysts (BAs) are not even aware of the great new tools that are available to help them succeed in their jobs and careers.  […] Read More
Combine Excel Workbooks with Power Query – Method 2 - Edit: Feb 2017.  Microsoft has released an update that solves the problem described in this article.  This article still has learning value however you should also take a look at the announcement from Microsoft here https://blogs.office.com/2017/02/08/january-2017-updates-for-get-transform-in-excel-2016-and-the-power-query-add-in/ There are many ways to combine data from multiple files into a single file/table in Excel.  In my first article […] Read More
Combine CSV Files with Power Query - This is the second of a series of articles where I explain different methods of combining data from multiple files into a single workbook/table.  In the first article I described how to use a function to combine data from identical Excel Workbooks into a single file using Power Query.  Next week I will cover a […] Read More
Combine Excel Workbooks with Power Query – Method 1 - I have blogged about these concepts before but thought there was value in creating a couple of clear blog posts covering step by step instructions on 2 good ways to combine multiple Excel Workbooks with Power Query.  Both of these approaches require that the Excel files are identical in structure (same column names, same number […] Read More
Convert Monthly Budget into Weekly Buckets 445 Calendar - I had an interesting problem this week with a client that uses a 445 calendar.  The problem is that they had some monthly budget figures that they wanted to track against actual sales on a weekly basis.  We agreed to do a straight line split of the budget into weeks (ie the weekly budget will […] Read More
Traditional Pivot Tables using Power Query – Can Do - Power Pivot and Power Query are the best things to happen to Excel since the invention of Excel itself.  Once you start using these tools there is no turning back.  But someone asked me the other day “Is it possible to use Power Query with a traditional pivot table?” (ie not with Power Pivot).  Indeed […] Read More
Microsoft Fixes A Couple of Excel Bugs - Microsoft has released a couple of long over due fixes to some long standing Excel 2013 bugs, both of which have been quite painful in their own way.  Unfortunately for me the timing couldn’t have been worse.  I literally just placed the order for the print run of my new book Learn to Write DAX, […] Read More
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 […] Read More
M is for Data Monkey - Digital Edition
M is for Data Monkey Book Review - The 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 […] Read More
Power Query as an Audit Tool - Level: Beginners Recently I was working for a client that required the matching of a list of data sent from a customer against some internal business data.  This type of data matching is actually an audit process that I would have historically done with MS Access, but it occurred to me that Power Query would be […] Read More
Brisbane and Sydney Training – it’s not too late to register - PowerPivot for Excel Live Training in October I have 2 training events scheduled in October and it is not too late to register. Brisbane – next week Mon 12th and Tues 13th October This one is next week, but there are still a couple of places left if you have time to attend (despite a late […] Read More