Archive - Blog Posts on Power Query

Import First Sheet with Power Query
Import the First Sheet in a Workbook with Power Query - My series about common patterns for loading data using Power Query received quite a lot of interest over the last few weeks.  You can review those articles in the links below Combine all files in a folder with Power Query. Load the Latest Version of a File with Power Query. Combine all sheets in a […] Read More
Combine All Sheets in a Workbook with Power Query - There are quite a few different common business scenarios where you get data in the form of a file on a regular basis.  The 3 common patterns I use most often are. Each day/month/year you get a new file containing incremental data.  All the data in all the files need to be combined and loaded. […] Read More
Best Book for Learning the M Language - Late in 2018 there was a new Power Query book written by Gil Raviv called Collect, Combine and Transform Data Using Power Query in Excel and Power BI.  That’s a bit of a mouthful actually; maybe the title was influenced by the same people that call Power Query “Get and Transform” in the Excel menu?!   […] Read More
Load latest file in Power BI
Load the Latest Version of a File with Power Query - There are quite a few common business scenarios where you get data in the form of a file on a regular basis from some source system and you need to process these files into your Power BI reports.  There are 3 common patterns that come to mind. Each day/month/year you get a new file containing […] Read More
Power BI Desktop Release Date History – Using Power BI - I was chatting to a few of my Power BI buddies during the week about when we thought the December 2018 update would be released.  It seemed to me that under normal circumstances the new version is released on or about the first Monday (US time) on or after the 3rd or 4th day of […] Read More
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