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

DescriptionLink
What’s New in Power BI?community.powerbi.com/t5/Data-Stories-Gallery/What-s-New-in-Power-BI/
Theme templates for Power BI visualsdataveld.wordpress.com/2018/01/20/template-theme-files-for-power-bi-visuals/
Bug Reportingcommunity.powerbi.com/t5/Issues/idb-p/Issues
The official Microsoft Power BI Blog; with regular updates about everything that is new.blogs.msdn.com/b/powerbi/
Power BI Helperradacad.com/power-bi-helper
Power BI Desktop – Terms and Definitionsblogs.msdn.microsoft.com/samlester/2017/04/12/power-bi-desktop-terms-and-definitions/
DAX Formatter. This is a great tool to format your DAX code using spacing and new lines to make it easier to readwww.daxformatter.com/
DAX Patterns. This is an interesting website that explains different DAX concepts and ‘how’ they can be reused to get particular outcomeswww.daxpatterns.com/
DAX As a Query Language This is a good blog post I found that covers the basics of DAX as a query languagewww.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.powerbi.microsoft.com/en-us/documentation/powerbi-refresh-desktop-file-onedrive/ or community.powerbi.com/t5/Desktop/Using-BI-Desktop-with-Excel-File-in-OneDrive-for-Business/m-p/18566#M5511
Power BI Support; Ideas and Self Helppowerbi.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 Gilbertpowerbi.microsoft.com/en-us/blog/tech-tip-thursday-sharing-your-data/
Power BI 12 month free trialhttps://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 BIhttps://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 thisjavierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/
Grid Generatorwww.fredrikhedenstrom.com/gridgen
Book list from Power BI Centralhttps://powerbicentral.net/books-about-power-bi/
Power BI Features Listhttp://aka.ms/pbifeatures
Theme File Generatorhttps://powerbi.tips/tools/report-theme-generator-v3/
Best design practices for reports and visualshttps://docs.microsoft.com/en-us/power-bi/power-bi-visualization-best-practices
Grid Generatorwww.fredrikhedenstrom.com/gridgen
Book list from Power BI Centralhttps://powerbicentral.net/books-about-power-bi/
Power BI Features Listhttp://aka.ms/pbifeatures
Power BI Weekly A free weekly newsletter which collates articles from the whole of the Power BI ecosystemhttps://powerbiweekly.info/
Power BI Blogmap – The blogmap curates a list of Power BI-related blogs.https://powerbi.world/
Power BI Blogs interactive reporthttps://powerbicentral.net/blogs-about-power-bi/?utm_source=powerbi&utm_medium=social&utm_content=power-bi-blog&utm_campaign=Blog+Posts

Power Query Links

DescriptionLink
Power Query TechNet Forumsocial.technet.microsoft.com/Forums/en-US/home?forum=powerquery
Use Power Query to combine data from identical files into a single tabledatapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/
Use Power Query to Access Files and Document Libraries in SharePointthesharepointfarm.com/2013/08/excel-power-query-loading-sharepoint-document-library-data/
Power Query M Language Reference Documentationhttps://docs.microsoft.com/en-us/powerquery-m/
How to fix the error microsoft.ace.oledb 12.0 provider is not registered erroroffice.microsoft.com/en-us/excel-help/connect-to-an-excel-or-csv-file-HA104019757.aspx
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 Foldingwww.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/
Excellent Video on Advanced uses for Power Query. This is a great link to a Power Query cheat sheet that outlines a lot of common uses and syntax.channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DBI-B410#fbid=

Power Pivot Links

DescriptionLink
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
Combine more than one file of identical structure into a single table in PowerPivot by editing the Data Sourcewww.contextures.com/PowerPivot-Identical-Excel-Files.html
Power Pivot Reference Cardpowerpivotpro.com/2015/10/giving-back-steal-this-reference-card/
What is eating up my memory the PowerPivot / Excel editionwww.powerpivotblog.nl/what-is-eating-up-my-memory-powerpivot-excel-edition/
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/
Power Pivot Broken? Recently I had an issue where PowerPivot for Excel 2013 just stopped working. I tried all the normal tricks of disabling the addin and re-enabling it. This time it was really broken. I found this link; ran the automatic repair tool for PowerPivot (there is one for Power View too) and all fixed. Another option to try is first to run Excel in Safe mode (goto run and type “Excel.exe /safe” without the quotes); then turn off all Com Addins; then shut down Excel; restart in normal mode and turn them back on.blogs.technet.com/
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 linkwww.powerpivotpro.com/2014/07/adding-tables-to-a-model-from-vba-in-excel-2013/
Unnofficial Memory Patch for 32 bit Excelntcore.com/4gb_patch.php/
Official Microsoft Memory Patch for 32 bit Excelsupport.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/
OLAP Pivot Table Extensions for Excel 2016.olappivottableextend.codeplex.com/
Chart of Accounts/Income Statement – Derek Rickard YouTube Videowww.youtube.com/watch?v=ojHZkWkEY7Q/
Migrating from SSAS Tabular to Power Pivot. Nice article from Gerhard Bruecklblog.gbrueckl.at/2014/05/restoring-a-ssas-tabular-model-to-power-pivot/
DMV to extract measures from a Power Pivot Data ModelClick to download
Connect to the Power Pivot Data Model from an External Program (Marco Russo)sqlblog.com/blogs/marco_russo/archive/2014/02/26/connecting-to-powerpivot-from-an-external-program-such-as-tableau.aspx

Excel/VBA Links

DescriptionLink
An excellent learning resource for learning VBAwww.vbaexceltutorial.com
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 VBAwww.contextures.com/xlPivot11.html
Ever want to give Microsoft feedback about Excel; or influence a new feature? This is the site you need.excel.uservoice.com/
Need help from Microsoft to get Power Pivot working with your version of Office? Go to this link and select “Office”support.microsoft.com/en-us or answers.microsoft.com
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/
Turn off time grouping in PivotTables in Excel 2016 for Windows. A word of caution: The solution describes how to make changes to the Windows Registry. Before editing the registry back up any valuable data. Use Registry Editor at your own risk.https://support.office.com/en-us/article/Turn-off-time-grouping-in-PivotTables-in-Excel-2016-for-Windows-6be5afed-348c-4db2-9f87-5ac262d67b3f?ui=en-US&rs=en-US&ad=US&fromAR=1
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
Identifying PivotTable Collisionshttp://dailydoseofexcel.com/archives/2017/12/08/identifying-pivottable-collisions/

Interesting Free Data Sets

DescriptionLink
NSW Crime Statsdata.gov.au/dataset/nsw-crime-data
Various World Stats – Hans Rosling sitewww.gapminder.org/
Good free calendar for PowerPivot from the Azure Marketplacedatamarket.azure.com/dataset/boyanpenev/datestream

Other

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

Two weeks to go! Power BI online DAX training starts on 28/29 January.  Now is the time to register to supercharge your Power BI skills.