Archive - Blog Posts on Power Query

Rename Blank in a Slicer in Power BI - A common problem I see some people struggle with in Power BI is when a slicer contains a (Blank) record for some reason.  The cause of the problem is not obvious and hence it is not clear how to fix it.   This article describes what causes this to occur and also how to fix it […] Read More
Importing XLSB into Power BI - I had a question from one of my online Power BI Training students just this week who was struggling to import data from a large *.xlsb Excel file.  I suggested she save the file as an XLSX file and try again – and that worked a treat.  But the question is why?  I had an […] Read More
Moving Queries from Power BI Desktop to Dataflows - A few weeks ago I wrote an article introducing dataflows in Power BI and discussed why you should care about them.  There are a few questions that have been spinning around in my head since then, significantly: What implication is there to the Power BI Golden Dataset as a result of the new dataflows? Should I […] Read More
Power BI Dataflows, and Why You Should Care - Microsoft recently released a new feature in Power BI called “Dataflows”.  You may have seen it pop up in a recent release of Power BI Desktop under Home\Get Data\Power BI Dataflows (beta) as shown below. It is also visible when you log into PowerBI.com and go to an App Workspace as shown in 1 below.  […] Read More
Data Profiling in Power BI - Today Microsoft released the October 2018 update of Power BI Desktop.  One of the fabulous new features that has been released is the ability to get a visual profile of your data in Power Query BEFORE you load the data.  This new feature is typical of the type of development effort Microsoft is investing in […] Read More
How Query Folding Works - Level: Beginners Power Query is a product that allows business users to extract data from any source, cleanse and transform that data, and then load the modified data to a new location, typically into an Excel spreadsheet or a Power BI data model.  Power Query is very easy to learn, and most tasks can be […] Read More
My Presentations at the MS Summit and Portland UG - Evaluation contexts in Power BI DAX I was in Seattle, USA in the last week of July participating in the Microsoft Business Applications Summit. I love attending conferences like this and it was a privilege to present on a few topics over 3 days.  One of the presentations I gave was on the topic “Evaluation contexts […] Read More
Power Pivot Global Interest - Way back in 2014 I decided to check out what Google Trends had to say about Power Pivot and Power Query global Interest. In case you don’t know, Google Trends will show you the relative frequency of people searching for certain words – it is a good indicator of interest in a topic.  I published […] Read More
Extracting Complex Data from Websites - One thing I did a long time ago was set up my own forum called PowerPivot Forum. This forum is open to anyone to join and to ask questions and/or help others. If I have any spare time (decreasingly so these days), I help people out on the forum too.  It occurred to me that the forum […] Read More
Happy Birthday Power Query - Power BI gets a lot of love – and it deserves it.  But under the hood you will probably know that there are 2 technologies that are the true work horses that prepare the data for use.  Those 2 products are Power Query and Power Pivot.  Power Query is the data acquisition software that goes […] Read More
Using Power Query for Extracting Non-Tabular Data from Web Pages - Matt Here:  I first met Ivan via Twitter when he was living in Russia, and then in person when he moved to Australia to live in Sydney with his family. Ivan is a talented Power BI professional and I have learnt many things from him over the last few years.  Today Ivan is writing a […] Read More
Time Intelligence Using DAX - Level: Intermediate Last week I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence.  This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time (see below). This article is a follow up from that webinar with […] Read More
Build a Reusable Calendar Table with Power Query - I wrote a blog on this topic a few years ago over at PowerPivotPro.com.  I have learnt a lot about Power Query since that time, plus Power Query has come along in leaps and bounds, and it now has a lot more capability than it had previously.  Today I am going to write a new […] Read More
Open the Queries and Connections Pane Wider - Power Query for Excel has developed at a striking pace over the last few years making it hard to keep up with the changes.  Over several iterations, the Queries and Connections pane has been called various names, and the behaviour has changed a few times too.  Depending on the version of Excel and Power Query, […] Read More
Pass an Excel Parameter to Power Query - Where has Matt been? Regular readers of my blog may have noticed I have been missing in action over the last month.  I have been super busy with the PUG World Tour, delivering a lot of training, finalising my new book Supercharge Power BI, and also taking a couple of weeks vacation. Side Story: Power […] Read More
Understanding Power Query Combine - Level: Intermediate Sometime late in 2016 Microsoft deployed a new “Combine” feature in both Power Query for Excel (Get and Transform) and also in Power BI Desktop (Get Data).  The new capability makes it easier to combine multiple copies of similar workbooks into a single table without hand coding M Functions, but unfortunately it is now […] Read More
My MDIS Data Analytics Challenge Entry - I was reading a blog by David Eldersveld last week where he was talking about his entry in the Microsoft Data Insights Summit Data Analytics Challenge.  I originally was too busy to enter however I was inspired by his article and thought I should prepare an entry albeit with only a few days to go.  […] Read More
Quick Measures and Query by Example - 2 fabulous new updates again in the April update of Power BI Desktop.  Here is my quick review so you can see how great these things are. Quick Measures Quick measures are designed to help you get started with DAX, without knowing any DAX!  OMG I will be out of a job!! Once again this […] Read More
Use Power Query to Compare Database Records - Level: Intermediate I was helping a user on http://community.powerbi.com this week. The user had a list of service desk tickets for multiple customers and the requirement was to identify when a customer opened a second new ticket within 7 days of closing a previous ticket.  This is not an uncommon scenario so I thought it would […] Read More
Best Practices for Power Pivot, Power Query and Power BI - Level: Beginners There are many best practices for Power BI, Power Pivot and Power Query.  I know these things so intuitively now that it is very easy to forget how I incrementally learnt these things along the journey.  Most of these things are not “right” vs “wrong” – they are more often simply “better”.  I thought there […] Read More