Archive - Blog Posts on Intermediate

How and Where to Get Help for Power BI - Regardless of what stage you are at in your Power BI/Power Pivot/Power Query journey, at some stage you are going to need some help.  Believe me I am no different to you – we are all learning and we all need help sometime or another.  There are basically 3 options that I want to talk […] Read More
Uses for DAX as a Query Language - This is the final article in my series about DAX as a Query Language.  In my first article I introduced DAX as a Query Language and then in my second article I went in deeper to the specifics on how to use some of the more useful DAX query functions.  In this final article today […] Read More
DAX as a Query Language Part 2 - In my last article I introduced DAX as a Query Language and explained why learning to query your data model can be useful.  I covered the basics about using a tool like DAX Studio to query a database and return a table of data as a result.  In this article I am going to go […] Read More
An Introduction to DAX as a Query Language - Level: Intermediate I have written quite a few articles that contain embedded references to DAX Queries and DAX Table functions in the past.  Today I am writing an article that introduces the DAX query language from the beginning so you can learn how to use it and how it will help you to become a […] Read More
SQL Saturday Melbourne Feb 2017 Materials - I am currently in Melbourne for PASS SQL Saturday 2017.  SQL Saturday is an annual one day conference event that occurs in many major cities around the world.  It is focussed on helping SQL Server professionals learn more about their profession and also network with other like minded people.  I have a lot of people […] Read More
Use Power Query to Compare Database Records - 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 make […] Read More
Extract Tabular Data From Power BI Service to Excel - Someone asked me a question yesterday about exporting data from the Power BI Service into Excel.  There are a few options to do this however they all have their problems (these problems are not covered in great detail in this post). Power BI has an inbuilt export data feature (there is an export limit of […] Read More
Data Modelling with OneDrive Source Files - Last week I shared how it was possible to easily create an online survey using Excel for O365.  Once such a survey is created, the data can be imported into PowerBI.com and used to create a simple report.  The biggest issue with directly connecting to OneDrive like I showed last week is that you can’t […] Read More
Import Tabular Data from PDF using Power Query - Today I am sharing a process I developed that allows you to import tabular data from a PDF document into Excel (or Power BI) using Power Query.  I didn’t want to purchase software to do this task so I started experimenting on how I could do it with the tools I already have, and I […] 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
Measures on Rows – Here is How I did it - Level: Intermediate You may or may not be aware that it is not possible to put Measures on rows in a Matrix in Power BI. But I came up with a trick that makes it possible, so read on to find out how. Measures Can Only be Placed on Columns First the problem. The only […] Read More
Power Query Over a Command Screen Output File - Level: Intermediate Power Query. 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 […] Read More
LASTNONBLANK Explained - Level: Intermediate Last week at my Sydney training course, one of the students asked me a question about LASTNONBLANK.  This reminded me what a trickily deceptive function LASTNONBLANK is.  It sounds like an easy DAX formula to understand, right?  It just finds the last non blank value in a column – easy right?  Well it […] Read More
A Double CALCULATE Solves a SUMX Problem - Level: Intermediate I helped a member at http://powerpivotforum.com.au with a problem last week that ended with an interesting solution.  The explanation of how it worked was a bit complicated and worthy of sharing, hence this is the topic of today’s post. Count the Working Days Between Two Dates The requirement was to count the working […] Read More
Power BI Personal Gateway Explained - One of the many excellent sessions I attend this week at the PASS Business Analytics Conference in San Jose was a session titled “Get Latest Insights by connecting your data using Power BI Content Packs and PBI Gateways”.  The title was interesting but the content presented by Dimah Zaidalkilani and Theresa Palmer-Boroski (both Program Managers […] Read More
Banding in DAX - Level: Intermediate As regular readers of my blog would know, I enjoy helping people on forums who need help with DAX, Power Query or Power BI.  Recently I have answered quite a few questions for people that could benefit from a DAX “Banding” solution and hence I have decided to write this post as a […] Read More