Useful Resources - Excelerator BI

Useful Resources

There are a lot of really smart people that work with Power BI, Power Pivot and Power Query and share their ideas on how to solve particular problems. This is a page of links that I have found that solve particular problems. I am sharing them here so others can use them, but also so I can find them again next time I need them.

Power BI Links

Description Link

What’s new in Power BI? The official Microsoft Power BI Blog; with regular updates about everything that is new.

Data Stories Gallery – A showcase of data storytelling brought to life.
Themes for Power BI Reports (downloadable .json files)
Bug Reporting
Power BI Helper

Power BI Documentation

DAX Reference
DAX Formatter

This is a great tool to format your DAX code using spacing and new lines to make it easier to read
DAX Patterns.

This is an interesting website that explains different DAX concepts and ‘how’ they can be reused to get particular outcomes
DAX As a Query Language

This is a good blog post I found that covers the basics of DAX as a query language
DAX Black Magic

This is a good blog post I found that explains how “black magic” works where the data table filters the lookup table. Warning; you really need to have some understanding of SQL and DB design to understand this.
How to create a local Power BI desktop file and have it sync automatically to the Power BI service with OneDrive.
Power BI Support; Ideas and Self Help
An excellent article and video from “Guy in a Cube” Adam Saxon about the various options to share your Power BI reports in the Power BI Service. Excellent Infographic of Free vs Pro features from Gilbert
Power BI free service and Power BI Pro free trial
Power BI White Papers Calculate distance between 2 points in Power BI
Nested Filters in Measures I was doing some work for a client and need to work out how to refer to an Earlier row context in a Measure. Quick search found this
Power BI Grid Layouts (Free!)
Power BI Licenses and Subscriptions
Theme File Generator
Best design practices for reports and visuals
Microsoft Power BI Blog
Power BI Weekly A free weekly newsletter which collates articles from the whole of the Power BI ecosystem
Power BI Blogmap – Power BI Blogs interactive report – Downloadable PDF
Power BI Cleaner tool to remove unused columns and/or measures from the data model.
Tool to compare 2 versions of Power BI files – Code, run, and share DAX
DAX Studio and Power BI diagnostic port (Marco Russo)
Microsoft Q&A (Q&A Forum for all Microsoft Products)

Extract Measures from Power BI into Excel and Text File using DAX Studio and Tabular Editor

Power Query Links

Description Link
Extracting All The M Code From A Power BI Dataset Using The DISCOVER_M_EXPRESSIONS DMV
Use Power Query to combine data from identical files into a single table
Use Power Query to Access Files and Document Libraries in SharePoint
Power Query M Language Reference Documentation
A fix for the dreaded Power Query error “Please remove and re-add the connection”
Query Folding
This is a collection of tips on how to improve performance of your M-queries.

Power Pivot Links

Description Link
Which versions of Office have Power Pivot?
Power Pivot – Overview and Learning
Power Pivot Help
Combine more than one file of identical structure into a single table in PowerPivot by editing the Data Source
Power Pivot Reference Card
PowerPivot and SharePoint. This is a very good article that talks about the limitations of hosting PowerPivot Workbooks on SharePoint.
Memory Consumption. A colleague of mine Scott Senkeresty at Tiny Lizard recently did some investigation into Kasper de Jong’s PowerPivot Workbook Memory VBA code. He has enhanced that code and made a new more complete version available. You can find it at the provided link.
VBA Data Model for PowerPivot There was a very good blog post by Dany Hoter on Rob’s blog that explains how to use the VBA data model for PowerPivot v 2013. Here is the link
Unnofficial Memory Patch for 32 bit Excel
Official Microsoft Memory Patch for 32 bit Excel
Excel 2016 losing data model on save? There’s a fix for that.
Chart of Accounts/Income Statement – Derek Rickard YouTube Video
Migrating from SSAS Tabular to Power Pivot. Nice article from Gerhard Brueckl
DMV to extract measures from a Power Pivot Data Model

Excel/VBA Links

Description Link
Count unique values in a list using the VBA Collection Object. Look for the solution referencing the Collection Object.
A great resource with lots of samples of how to work with Pivot Tables using VBA
Need help from Microsoft to get Power Pivot working with your version of Office? Go to this link and select “Office”
A nice way to provide a list of values in an Excel workbook; and then apply that list as a filter in a Pivot Table.
This article shows you how to use VBA to change a cell value in a closed Excel workbook.

Interesting Free Data Sets

Description Link
NSW Crime Stats
Various World Stats – Hans Rosling site


Description Link
Magnify/Zoom any screen in windows – Useful if you need to share a screen and zoom in to show formulas
Scroll to Top