Welcome to the Excelerator BI Knowledge Base.
The objective of this Knowledge Base is to provide a one-stop shop of information to help people like you get started (and grow to love) Power BI, Power Query, Power Pivot and all the other great software products that are associated with Microsoft Self Service BI. The content will continue to grow over time, so check back often to see what is new.
The sub menu shows 2 categories of the content – Getting Started and Key Concepts. The content under Getting Started is for those who are planning to use or just started using these tools. Key Concepts has the content that is required to learn more about the tools. Below is a summary of each of the topics in the knowledge base menu.
30 Reasons To Consider Power BI
I wrote this article to be a definitive list of the top reasons you should consider using Power BI in your organisation (in case you haven’t already made the decision to do so).
https://exceleratorbi.com.au/top-30-reasons-you-should-be-considering-power-bi/
Understanding Power BI
Microsoft Power BI is the single most important thing to happen in the data analytics market since the introduction of Microsoft Excel 30 years ago. So Power BI has become an important product to know about. And, Power BI has the umbrella products Power Pivot and Power Query.
So if you want to become a Power BI ninja, you need to learn Power Pivot and Power Query.
You can understand about Power BI, Power BI Branded Products and Power BI Umbrella Products in this article.
You can even get to know about the best resources for learning these tools.so that you can choose the option based on your learning style.
If you think you need to know more about Power Pivot and Power Query before you decide on learning these products, you can read the articles given below.
https://exceleratorbi.com.au/what-is-power-bi/
What is Power Pivot
Power Pivot is a tool that allows you to prepare your data in a way that Pivot Tables can use. Power Pivot is actually a data modelling tool. You can understand Power Pivot and even get to know about the best resources for obtaining useful information about Power Pivot and learning Power Pivot here.
https://exceleratorbi.com.au/what-is-power-pivot/
Which Versions of Excel come with Power Pivot?
One very common question people ask is “which versions of Excel come with Power Pivot”? Until recently not all Microsoft Office products included Power Pivot. But Microsoft listened to the community and made an announcement that Power Pivot will be made available in all Windows editions (“SKUs”) of Excel starting from April 2018! However there are still exceptions with some of the Excel 2013 versions. You can know about them here.
https://exceleratorbi.com.au/which-versions-of-excel-come-with-power-pivot/
What is Power Query
Power Query (aka Get and Transform) is an Extract, Transform and Load tool (ETL) that helps Excel and Power BI users connect to data, transform the shape of the data as required and then load the data for further use, typically into Excel, Power BI, CSV etc. ETL tools are not new – there are many enterprise strength tools in the marketplace that are used by IT professionals. What makes Power Query such a ground-breaking tool is it is the first tool built specifically for business users. You can read more about Power Query in this article.
https://exceleratorbi.com.au/what-is-power-query/
Benefits of Power BI, Power Pivot
& Power Query
Are you still wondering how Power BI, Power Pivot and Power Query can help you in your job? In this article I explained who can benefit from these tools and how they are handy in various aspects. So read on …
https://exceleratorbi.com.au/who-needs-power-pivot-power-query-and-power-bi-anyway/
How you can learn from me
Power BI is an enigma. On the surface Power BI is an alluring product that promises to change your world of data analytics for the better – and indeed it can and will. But sitting just beneath the surface, hidden from view, is the reality that there are many things you must learn if you want to be competent using the new breed of business intelligence tools. There are quite a few ways you can learn from me if and when you are ready to do so. Read here to get the details.
https://exceleratorbi.com.au/power-bi-is-an-enigma/
Power Downloads
It can be reasonably difficult to find the exact download you need for the various Power Products in the Microsoft BI stack. Some of these products (particularly Power Pivot) have a number of versions. This article is provided to help you get the correct download first time.
https://exceleratorbi.com.au/power-downloads/
Installing Power Pivot/Power Query for Excel 2010
As most subscribers of my blog would already know, I am a professional trainer of Power BI here in Australia. I do a lot of work for Corporations at their own premises with their own PC equipment. Not every company has made the move to Excel 2013, and in fact I sometimes find companies have to move from Excel 2007 to 2010 just to prepare for this training. There are a couple of traps when installing the Power Pivot for Excel 2010 Plug-in (and the Power Query Plug-in for that matter) and hence I have written these instructions to help people and companies get it right the first time.
https://exceleratorbi.com.au/correctly-install-power-pivot-excel-2010/
Top 10 Tips for Getting Started with Power BI
So at this step, you might have decided to use Power BI. I really love Power BI, and I have learnt so much that sometimes it is easy to forget the challenges I had in getting started. I shared my top 10 tips on how to get started with Power BI in this article.
https://exceleratorbi.com.au/top-10-tips-getting-started-power-bi/
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. Over the last few years I have trained several candidates on Power BI/Power Pivot/Power Query and my teaching skills improved while I was interacting with the students. In this article, I discussed various options for your to get support on Power BI.
https://exceleratorbi.com.au/get-help-power-bi-2/
What is DAX
Data analysis expressions (DAX) is the data modelling language of Excel Power Pivot and Power BI.Power BI is better when you learn to write DAX. You can know more about DAX in my article –
https://exceleratorbi.com.au/what-is-dax/
An Introduction to DAX as a Query Language
DAX can also be used as a query language to extract a subset of your data so you can take a look at it. In this article I introduced the DAX query language from the beginning so you can learn how to use it and how it will help you to become a Power BI superstar.
https://exceleratorbi.com.au/introduction-dax-query-language/
Best Practices for Power BI, Power Query & Power Pivot
There are many best practices for Power BI, Power Pivot and Power Query. I know these things so intuitively now that it is very easy to forget how I incrementally learnt these things along the journey. Most of these things are not “right” vs “wrong” – they are more often simply “better”. I thought there would be value in producing a page that clearly outlines the important best practices as a learning reference for everyone here.
https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/
Power BI Skills – Thinking of Data in Tables
One very important concept that Excel users often struggle with when starting on their Power BI and Power Pivot journey is that they need to learn to think about data in tables rather than thinking about data in cells. In this article, I illustrated how to think of data in tables and columns and ho to have a view of the entire data model.
https://exceleratorbi.com.au/power-bi-skills-thinking-data-tables/
Shaping vs Modelling in Power BI
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. But there is no reference to the terms “Power Pivot” and “Power Query” inside the Power BI tools. I like the term “Shaping” as a replacement for Power Query/Get Data and “Modelling” as a replacement for Power Pivot. In this article, I explained Shaping and Modelling and when to use what.
https://exceleratorbi.com.au/shaping-modelling-power-bi/
The Optimal Shape for Power BI Data
One of the topics in my book “Supercharge Power BI:Power BI is Better When you Learn to Write DAX” talks about the optimal shape of your data tables. It is such an important topic that I have decided to share some modified extracts from the book in this article.
https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
Relationships in Power BI
Power Pivot is a database that has been designed from the ground up to be optimised for reporting and analysis in Power BI, Power Pivot for Excel and SSAS Tabular. I explain what you need to know to get started with relationships in Power Pivot here.
https://exceleratorbi.com.au/relationships-power-bi-power-pivot/
Calculated Columns vs Measures in DAX
In this article, I explained the benefits of using Measures instead of Calculated Columns. I have also given my general rules for when it is OK to use Calculated Columns.
https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
DAX Time Intelligence Explained
If you want to do any time based comparison using DAX, Power Pivot and or Power BI, then this article explaining time intelligence is a good read for you.
https://exceleratorbi.com.au/dax-time-intelligence-beginners/
I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence. This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time
https://www.youtube.com/watch?v=Ju_JURCHA6o
Power BI Calendar Tables
In this article I explain the reasons to use a Power BI calendar table, the rules for a calendar table, and additional features of a good calendar table.
https://exceleratorbi.com.au/power-bi-calendar-tables/
When to Create a Lookup Table in Power Pivot
In this article I explained when it is important to create a lookup table and when it is fine to use native columns in a data table.
https://exceleratorbi.com.au/create-lookup-table-power-pivot/
Multiple Relationships Between Tables in DAX
In this article, I explained why you might need multiple relationships between tables, how to create then and how to use inactive relationships.
https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/
Multiple Data Tables in Power BI
Power Pivot is a very capable tool for building reports that combine multiple data tables. Although it is very capable, the logic of how to combine multiple tables is different in Power Pivot than most Excel users will be familiar with. This post covers the correct way to do it.
https://exceleratorbi.com.au/multiple-data-tables-power-pivot/
Visually Appealing Power BI Reports
In this article, I addressed the question that I often get asked – “How do I create good looking Power BI Reports?”
https://exceleratorbi.com.au/visually-appealing-power-bi-reports/
Top Tips for Sharing Content Using Power BI
There are several sharing approaches in Power BI. It is not easy to categorise the sharing approaches into logical buckets and there is quite a bit of overlap. In this article, I shared my top tips about how you can share, and when you should use each of the various methods.
https://exceleratorbi.com.au/top-tips-for-sharing-content-using-power-bi/