How to Document DAX Measures in Excel

How to Document DAX Measures in Excel

I often get asked if there is an easy way to create documentation for DAX measures when using Power Pivot for Excel.  I am not a big fan of documentation for the sake of it, but I do see value in having “some” appropriate level of documentation.  I think a good balance of the right amount of documentation vs the effort to write and maintain the documentation is to use the Power Pivot UI itse...
64 Bit Power BI Desktop with 32 Bit Office? Can do!

64 Bit Power BI Desktop with 32 Bit Office? Can do!

As many of you would already know, 32 bit applications do not always play well with their bigger sibling,  64 bit applications. There are lots of “under the hood” files and applications that are always designed to be either 32 bit or 64 bit - not both.  In the case of Microsoft Office, you must choose either 32 bit or 64 bit across the entire Office Suite.  You can’t for example ha...
Save PBIX from Power BI Service Under Delivers

Save PBIX from Power BI Service Under Delivers

This blog post was going to be about how excited I was to hear that there is now an option to download a PBIX file from the Power BI Service to your PC.  Unfortunately all that glitters is not gold.  This new option in the Power BI Service promises a lot but delivers little – let me explain. The Authoring Pattern The general pattern I recommend for Power BI is: Author your workbook ...
Import Tabular Data from PDF using Power Query

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 am sharing my solution here today. Note:  This will only work for tabular data in a PDF – exactly the same...
Excel Workbook Connection to Local Power BI Desktop

Excel Workbook Connection to Local Power BI Desktop

Today I am sharing an easy way to use Power BI Desktop as a SSAS tabular server on your local PC.  I find increasingly that I am building data models in Power BI Desktop and yet I still want to use Excel to analyse the data some of the time.  If I load the PBIX workbook to the Power BI Service, it is easy to use "Analyze in Excel" to query the data model.  But there is currently no standard sup...
Power Pivot Crashes? – An Update

Power Pivot Crashes? – An Update

Today Avi Singh and I met with Howie Dickerman from Microsoft to discuss the Power Pivot for Excel Add-In.  You can read the background to this meeting at my blog post last month.  Howie is the Program Manager assigned to Power Pivot for Excel and has a wealth of experience with Excel and other products.  I shared all the comments that people added to my blog post from 19th October.  The great...
When to Create a Lookup Table in Power Pivot

When to Create a Lookup Table in Power Pivot

Level: Beginners Today I explain when it is important to create a lookup table and when it is fine to use native columns in a data table.  I have rated this topic as a beginner topic as it is a fundamental skill to learn on your journey to become a Power Pivot and Power BI Ninja.  However there is so much to learn and you can only absorb so much content before your head begins to explode.  B...
Find Duplicate Files on Your PC with Power BI

Find Duplicate Files on Your PC with Power BI

Level: Beginners If you want to learn new skills using a new tool, then you simply must practice.  One great way to practice is to weave the new tool into you daily problem solving.  If you have something meaningful to do with the new tool, then you are much more likely to be motivated to practice.  And the new tool I am talking about of course is Power BI. Last week I showed how easy it ...
Use Power Query to Manage Dropbox Space

Use Power Query to Manage Dropbox Space

Level: Beginners I got this dreaded Dropbox email recently as shown below. I needed to clear out some of the files I have loaded in Dropbox so I didn’t have to upgrade my account.  It occurred to me that I could make this process a lot easier by using Power BI to quickly show me where my big files were located in Dropbox.  This post today explains how I did it. What I ended up with i...
Parramatta. The Power BI User Group is Coming to You

Parramatta. The Power BI User Group is Coming to You

I am the joint founder and host of the Sydney Australia chapter of the Power BI User Group (PUG) along with Iman Eftekhari from Agile BI.  Next week on Wednesday 26th October (for the first time) we will be holding a live meet up in Parramatta to make it easier for people that work in the area to get involved with the community.  Parramatta is the geographical centre of Sydney after all. The ...
Does the Power Pivot Excel Add-In Crash for You?

Does the Power Pivot Excel Add-In Crash for You?

I was chatting to Avi Singh the other day and we were comparing notes about the live Power Pivot training classes we both offer in Australia (me) and the USA (Avi).  One thing we both have in common is that the Microsoft Excel Power Pivot Add-In CRASHES a lot during training!  When I say "crash", I mean it stops working in some ungraceful way.  Excel normally don't stop working, just the plu...
Who Needs Power Pivot, Power Query and Power BI Anyway?

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 their jobs.  The situation is definitely getting better as Power BI s...
Query Dependency View in Power BI Desktop

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 Desktop. “What is this and why are you so excited Matt” I hear you ask. What is Dependency View Simply stated, it...
Shaping vs Modelling in Power BI

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 new wave of users that have never heard of Power Pivot and Power Query, and their fir...
PQL – Pronounced Peequel

PQL – Pronounced Peequel

Back in February 2016, I was tweeting with some others about the need for a new name for the Power Query Formula Language.  I came up with the name PQL, pronounced Peequel and made a suggestion to this effect at ideas.powerbi.com.  This post today is to explain why a name change is required, and to seek support for renaming the language to PQL. A Background on the Current Name(s) The official ...
Cumulative Running Total Based on Highest Value

Cumulative Running Total Based on Highest Value

Level: Advanced I’ve helped a couple of people out on various forums on this topic over the last week, and that is normally a good sign that I need a blog post on the topic. The scenario is to create a Pareto cumulative running total based on the top products, customers or whatever.  This is a bit tricker than a simple YTD running total, as the “order” of the best to worst products (or...
Top 10 Tips for Getting Started with Power BI

Top 10 Tips for Getting Started with Power BI

Level: Beginners I really love Power BI, and I have learnt so much over the last 12 months that sometimes it is easy to forget the challenges I had in getting started. Today I am sharing my top 10 tips on how to get started with Power BI. Build Your Reports in Power BI Desktop, Not the Service One thing that can be confusing is that there are 2 ways you can build reports – Power BI Deskt...
Conditionally Refresh Queries in Power BI

Conditionally Refresh Queries in Power BI

There was a great new feature that snuck in unannounced to the July release of Power BI Desktop.  It was only a tweet by my colleague Imke Feldmann from http://www.thebiccountant.com/ that I was even aware it was there. Include in Report Refresh When you look in the Queries pane in Power BI Desktop, you will see your list of queries in the Queries pane on the left hand side.  Simply right clic...
Convert a Cell Value into a Column with Power Query

Convert a Cell Value into a Column with Power Query

Level: Advanced Power Query I am in the process of finalising some action items after a great Power BI, Power Pivot and Power Query training course in Melbourne.  During the training we looked at a scenario for one of the attendees where they needed to combine data from multiple Excel files into 1 big table.  I have blogged about this before in 3 articles starting here. In this specific ...
Happy First Birthday Power BI

Happy First Birthday Power BI

One year ago today on the 24th July 2015 a brand spanking new BI baby was born.  And its proud parents named the new baby Power BI, and soon Power BI would become known to everyone around the world.  Today is therefore a day to celebrate what a great first year it has been for this bambino.   From Little Things, Big Things Grow It is no secret that I thought the baby was just a little bit...

Bring your data to life - Let's discuss how Matt can help!

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn Power Pivot Without The Pain!

x