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.

https://docs.microsoft.com/en-us/power-bi/fundamentals/desktop-latest-update?tabs=powerbi-desktop

Data Stories Gallery – A showcase of data storytelling brought to life.

https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery
Themes for Power BI Reports (downloadable .json files) https://community.powerbi.com/t5/Themes-Gallery/bd-p/ThemesGallery
Bug Reporting community.powerbi.com/t5/Issues/idb-p/Issues
Power BI Helper radacad.com/power-bi-helper

Power BI Documentation

https://docs.microsoft.com/en-us/power-bi/

DAX Reference

https://docs.microsoft.com/en-us/dax/
DAX Formatter

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

www.daxformatter.com/
DAX Patterns.

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

www.daxpatterns.com/
DAX As a Query Language

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

www.simple-talk.com/sql/reporting-services/using-dax-to-retrieve-tabular-data/
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.

mdxdax.blogspot.com.au/2011/03/logic-behind-magic-of-dax-cross-table.html
How to create a local Power BI desktop file and have it sync automatically to the Power BI service with OneDrive. https://docs.microsoft.com/en-us/power-bi/connect-data/service-get-data-from-files
Power BI Support; Ideas and Self Help powerbi.microsoft.com/en-us/support/
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 powerbi.microsoft.com/en-us/blog/tech-tip-thursday-sharing-your-data/
Power BI free service and Power BI Pro free trial https://community.powerbi.com/t5/Changes-to-the-free-Power-BI/May-3-announcement-FAQ/td-p/167399
Power BI White Papers Calculate distance between 2 points in Power BI https://powerbi.microsoft.com/en-us/documentation/powerbi-whitepapers/
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 javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/
Power BI Grid Layouts (Free!) https://powerbi.tips/product/layouts-grid/
Power BI Licenses and Subscriptions https://docs.microsoft.com/en-us/power-bi/consumer/end-user-license
Theme File Generator https://powerbi.tips/tools/report-theme-generator-v3/
Best design practices for reports and visuals https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-best-practices
Microsoft Power BI Blog https://powerbi.microsoft.com/en-us/blog/
Power BI Weekly A free weekly newsletter which collates articles from the whole of the Power BI ecosystem https://powerbiweekly.info/
Power BI Blogmap – Power BI Blogs interactive report – Downloadable PDF https://bi-survey.com/powerbimap/power-bi-blogs-overview/
Power BI Cleaner tool to remove unused columns and/or measures from the data model. https://www.thebiccountant.com/2020/01/01/tidy-up-power-bi-models-with-the-power-bi-cleaner-tool/
Tool to compare 2 versions of Power BI files https://www.thebiccountant.com/2019/09/14/compare-power-bi-files-with-power-bi-comparer-tool/
DAX.do – Code, run, and share DAX https://dax.do/
DAX Studio and Power BI diagnostic port (Marco Russo) https://www.sqlbi.com/blog/marco/2018/08/21/dax-studio-and-power-bi-diagnostic-port/
Microsoft Q&A (Q&A Forum for all Microsoft Products) https://docs.microsoft.com/en-us/answers/products/

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

https://www.youtube.com/watch?v=C6WgYUE7ZAw

Power Query Links

Description Link
Extracting All The M Code From A Power BI Dataset Using The DISCOVER_M_EXPRESSIONS DMV https://blog.crossjoin.co.uk/2019/05/08/extract-m-code-dmv/
Use Power Query to combine data from identical files into a single table https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries
Use Power Query to Access Files and Document Libraries in SharePoint thesharepointfarm.com/2013/08/excel-power-query-loading-sharepoint-document-library-data/
Power Query M Language Reference Documentation https://docs.microsoft.com/en-us/powerquery-m/
A fix for the dreaded Power Query error “Please remove and re-add the connection” exceleratorbi.com.au/solved-power-query-issues-with-power-pivot-exception-from-hresult-0x80020009/
Query Folding www.thebiccountant.com/2015/09/17/filter-sql-server-queries-with-excel-tables-query-folding-limitations-and-bug-workaround/
This is a collection of tips on how to improve performance of your M-queries. https://www.thebiccountant.com/speedperformance-aspects/

Power Pivot Links

Description Link
Which versions of Office have Power Pivot? https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b?ui=en-US&rs=en-US&ad=US&fromAR=1
Power Pivot – Overview and Learning https://support.microsoft.com/en-us/office/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80-028a6d2432ed
Power Pivot Help https://support.microsoft.com/en-us/office/power-pivot-help-241aac41-92e3-4e46-ae58-2f2cd7dbcf4f
Combine more than one file of identical structure into a single table in PowerPivot by editing the Data Source https://contexturesblog.com/archives/2010/09/06/powerpivot-from-identical-excel-files/
Power Pivot Reference Card powerpivotpro.com/2015/10/giving-back-steal-this-reference-card/
PowerPivot and SharePoint. This is a very good article that talks about the limitations of hosting PowerPivot Workbooks on SharePoint. whitepages.unlimitedviz.com/2013/08/power-bi-office-365-and-file-size-limits-be-careful/
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. tinylizard.com/2014/09/26/script-update-what-is-eating-up-my-memory-in-power-pivot/
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 www.powerpivotpro.com/2014/07/adding-tables-to-a-model-from-vba-in-excel-2013/
Unnofficial Memory Patch for 32 bit Excel ntcore.com/4gb_patch.php/
Official Microsoft Memory Patch for 32 bit Excel support.microsoft.com/en-us/kb/3160741/
Excel 2016 losing data model on save? There’s a fix for that. support.microsoft.com/en-us/kb/3114297/
Chart of Accounts/Income Statement – Derek Rickard YouTube Video www.youtube.com/watch?v=ojHZkWkEY7Q/
Migrating from SSAS Tabular to Power Pivot. Nice article from Gerhard Brueckl blog.gbrueckl.at/2014/05/restoring-a-ssas-tabular-model-to-power-pivot/
DMV to extract measures from a Power Pivot Data Model https://exceleratorbi.com.au/dmv-extract-measures-power-pivot/

Excel/VBA Links

Description Link
Count unique values in a list using the VBA Collection Object. Look for the solution referencing the Collection Object. stackoverflow.com/questions/23035511/count-the-unique-values-in-one-column-in-excel-2010-or-r-with-1-million-rows
A great resource with lots of samples of how to work with Pivot Tables using VBA www.contextures.com/xlPivot11.html
Need help from Microsoft to get Power Pivot working with your version of Office? Go to this link and select “Office” https://docs.microsoft.com/en-us/answers/products/
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. www.thebiccountant.com/2015/08/21/want-to-filter-a-pivottable-based-on-an-external-range-or-list/
This article shows you how to use VBA to change a cell value in a closed Excel workbook. https://stackoverflow.com/questions/27908561/use-excel-vba-to-change-the-value-of-a-cell-in-a-closed-workbook

Interesting Free Data Sets

Description Link
NSW Crime Stats data.gov.au/dataset/nsw-crime-data
Various World Stats – Hans Rosling site www.gapminder.org/

Other

Description Link
Magnify/Zoom any screen in windows – Useful if you need to share a screen and zoom in to show formulas www.brightcarbon.com/blog/magnify-or-zoom-into-any-content-or-screen-in-windows/
Scroll to Top