Data and AI Training
Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data
Power BI Overview
This is a whirlwind tour to introduce you to Power BI and help you decide if you want to invest time and effort in learning how to use it well.
What is Power BI?
Power BI is a (proprietary) business intelligence tool from Microsoft. It is the most popular BI software on the planet (according to Microsoft). Here is Microsoft’s Power BI marketing page.
Power BI as part of a data analyst’s armoury
My recommendation - use Power BI as one tool of many to help solve data challenges. Other tools may include:
- Python: the defacto open-source language of data analysis, data science and AI
- SQL: at least how to retrieve data with the SELECT statement
- Excel: has become more capable with data challenges in recent years: Python in Excel, Power Query (as with Power BI), and a beefed-up formula language that can operate of datasets and arrays
Why learn Power BI?
Because your organisation requires it. Doh! But also:
- it is a marketable skill $$$
- learn about data & analytics by osmosis, especially if new to this subject (as many people starting with Power BI are)
Resources to help learn Power BI
There are a lot of great resources available, for example:
- YouTube tutorials, some of very high quality
- in-person and virtual meetups.
- conferences such as sqlbits.
A short list of learning resources here.
A Getting Started in Power BI playlist on YouTube. Direct Link here.
Desktop and Service
Power BI has two parts:
- Power BI Desktop, a free application for building reports (Windows only)
- Power BI Service, a cloud service for report consumers: read and interact with reports
Power BI Desktop as of March 2025
Power BI Service as of March 2025
Power BI Desktop Components
Power BI Desktop stitches together several components:
- Query Editor: import, Transform and clean dataset, so that they are in good shape to analyse and visualise
- Report View : choose, configure, layout visuals on a canvas to create the report
- Data Modelling: arrange tables and columns and relationships
- DAX: the calculation and modelling language of Power BI
Skills needed for mastery of Power BI
Several skills are needed to master all of Power BI.
- shape in the query editor: improve data quality, reshape data: use operations such as merge tables, add columns, unpivot a table.
- build meaningful, useful, actionable reports: skills include business analysis, design effective visualisations, user interface and graphic design
- create a semantic model: knowledge of dimensional modelling, star schema pattern , fact and dimension tables
- code in DAX (which requires understanding of how Power BI works under the covers at a deep level)
Typically, one person does not have all of these skills. A team is often split into sub-teams:
- the model builders specialise in data shaping, data modelling and DAX publish a semantic data model to the service
- the report builders specialise in business analysis and graphic design and build the reports
The shortest history of the evolution of Power BI
Here is the shortest form of the long history of Power BI. It start with the heritage that goes back further than you may expect.
- 1950s: the Simulmatics Corporation: using data in 1960 US presidential elections. The story ios told in the book If Then.
- 1960s: expensive executive decision support systems such as Comshare
- 1970: the birth of SQL
The Power BI story proper starts more the a quarter century ago.
- 2000s: Microsoft “Cubes” (Analysis Services and the MDX language)
- 2009: Microsoft announced some new technologies: “tabular”, DAX (marketed initially as a simpler form of MDX) and Power Query: all squeezed into Excel add-ins (PowerPivot, PowerQuery, PowerView)
- 2015: Power BI announced. PowerPivot, PowerQuery, PowerView combined into single standalone application: Power BI Desktop. Tagline “5 minutes to wow”. Here is the official announcement.
- 2016 onwards to today: continuous improvements and extensions with monthly releases
- 2023: Microsoft Fabric announced; all things data engineering, data science, data analysis in one SaaS platforms: Power BI becomes part of Fabric
Power BI Desktop from the October 2016 blog post.
The Query Editor shapes and cleans data
The Query Editor is a thing of wonder! It allows us to import our data for all sorts of different sources (database, CSV, Excel, …), clean and shape our data to make it ready for visualisation and analysis. It has a user interface so we can apply the transformations that we need. Under the covers, it writes a language (called M) so these steps can be automated and data refreshed on demand or on schedule
Generative AI models, such as ChatGPT, can help a lot with Power Query and M.
Power BI Desktop’s Query Editor.
Power BI Desktop’s Views
Power BI Desktop has about 5 views. You will get to know these well. These are:
- report view: a canvas to chose, configure and format visuals to build the report design
- table view: a convenient wat of see the data of all tables imported into Power BI
- model view: the structure of the semantic model: tables, columns, relationships, formats, hierarchies, measures,..
- DAX query view: a proper editor to write and test DAX: much better than the formula bar
- TMDL view: a newish view that allows a code first approach to changing every aspect of the report: useful for advanced developers
The report view: canvas and panes
The report view has the report canvas and several panes. These are:
- the Data pane: the list of tables and columns - drag these onto a visual to configure
- the Build pane: shows the configuration of the model: maps fields (columns) to visual aspects. e.g. for a column chart, Place Region field on the x-axis
- the Format pane: the layout and aesthetics of the reports (colours, line-styles, data labels). A preview feature, on-object formatting can now do the basics of this in a more intuitive way.
- the Filters pane: provide a mechanism to add interactive filters. Three scopes: visual, page and all pages (entire report). This is an alternative to slicers.
Other panels are also available and occasionally useful.
Power BI Desktop’s report view with panes on the right. From right to left these are Data, Build, Format and Filters.
The Visual Gallery
The visuals gallery contains about 30 different types of visual. We can group these into:
- bar and column charts: stacked, clustered: 100% variations
- line charts (and area charts)
- combinations of line and bar charts
- pie charts and tree maps
- cards: important single values
- slicers: allow user to filter the data
- geospatial maps, of which the Azure map visual is the first-amongst-equals
- “AI-assisted” tools such as Key Influencers
Power BI Desktop’s visual gallery
Other tools are available
There are other tools as well as Power BI Desktop that allow report builders to see further under the covers. These are usual free or freemium licenses. They include:
- Bravo: shows size of columns in memory (smaller is better for performance). It also highlights fields in the data that are not used in the report
- DAX Studio: largely replaced by the introduction of the DAX query view but has the capability to look at the size of the model ina more detailed way than Bravo
- Tabular Editor: a code-first way of editing a Power BI file. Useful for advanced developers and complex models
There are available as standalone and also available as extensions to Power BI Desktop.
This Bravo model analysis shows column sizes and highlights columns that are not used on any visual on any report so could possibly be removed
Data Modelling is the fundamental challenge
The analytical power of Power BI comes from arranging several tables with relationships. Relationships match columns into two tables that have the same domain of values. This allows report builders to choose any set of columns from any table in the data model and place on to a visual and that visual will work properly - no need for any Excel XLOOKUP or SQL JOIN statements.
Power BI Desktop’s Model View.
DAX is the calculation and modelling language
The two things that you need to know about DAX before you start your journey
- DAX is very powerful. You need DAX to create many “interesting calculations” such as ratio of time intelligence e.g. year-to-date.
- DAX is hard. It does require time and effort to master.
If the previous statements cause you to despair, don’t.
- Microsoft have introduced Visual Calculations, “DAX Made Easy” with a point and click method for many standard business calculations
- Generative AI models can help a lot with DAX. They can write the DAX given the context and objective. They can explain the DAX and act as a teacher.
The DAX query view helps us write, test and understand DAX.
Power BI Enterprise Features
Enterprise features are more important to large development teams building complex sematic models. Microsoft have been retrofitting enterprise features into Power BI Desktop and Service over the last few years. These include:
- application lifecycle management: separate dev, test ad production environment and a structured method of deploying to these environments
- version control e.g. Git
- and of course, Microsoft Fabric: OneLake data lakes, Python and Spark as data engineering choices, SQL endpoints
Much of this work has required to Power BI Desktop’s internal file structure to a more open, documented format.