Why you are here

Your company creates data every day without anyone asking for it. Transactions, visits to the website, what people looked at and what they bought in the end, measurements from machines and services. That data is already there. The question is whether anyone gets something out of it.

Data is information, and information is what decisions rest on. If you see in March that a product is sliding, you adjust in April. If you only read it in December in an annual report, you are looking at history.

In practice you use that data to track inventory, to spot buying habits and trends, and to find patterns you miss with the naked eye. Decision makers need it to switch gears quickly, to change course and to stay ahead of the competition.

From raw data to a story

Before you can tell anything with data, it has to go through four steps. This course follows exactly that order, so you will recognize them later on.

1. Identify. What data do you have, and where is it.
2. Clean up. Get the errors, the duplicates and the empty fields out.
3. Transform and model. Tie the tables together into one whole.
4. Turn it into a story. Reports and dashboards that someone else can use.

Five kinds of analysis

Not every analysis answers the same question. These five keep coming back in the literature.

The diagnostic analysis runs in three steps: you look for the anomalies in the data, you gather everything that has to do with those anomalies, and you explain them with statistical techniques.

In this course you mostly stay with the first two. What happened, and why.

Words you are going to run into

• Telemetry data. Measurements taken at a distance, for example temperature, humidity or heart rate, often sent wirelessly to another location.
• KPI. Key performance indicator. Lets you measure the performance of your organization, team or staff.
• ROI. Return on investment. The return on an investment.

The three versions of Power BI

Power BI is not one single program but a set of tools. You use three of them in this course.

Power BI Desktop

A program you install on your own machine. Only available for Windows. This is where you create your semantic model. Desktop consists of four building blocks.

• Semantic model. The data or information in your data model. You connect Power BI Desktop to an existing semantic model, or you create one yourself. In that second case you connect to your source documents through so-called queries, you edit those queries, and you load the new structure into your data model.
• Visualizations. A chart, a table, or anything else that shows your data graphically.
• Tiles. The frames those charts sit in. You adjust height, width and position freely. The only limit is the size of your report page.
• Reports. Several pages with different visualizations, put together into one whole.

Power BI Service

The version in the cloud, so not tied to Windows. Because it does not sit on your own machine, you can share results with colleagues from here and work on a project together.

You find the same building blocks there as in Desktop, plus a fifth one.

• Dashboards. Think of a cover page. A single page with the most important figures from your report.

You make visualizations and reports in the Service in almost the same way as in Desktop.

So the approach in this course is: build in Desktop, publish to the Service, keep working there from another place or together with colleagues, and put a dashboard on top of it.

Power BI Mobile

An app for your phone or tablet. With it you view the data you put in the Service while you are on the move.

Power BI and Microsoft Fabric

Power BI does not stand on its own. Microsoft has built a broader platform around it, Microsoft Fabric. The distinction is simpler than it looks.

For this course that changes nothing about the way you work. You build in Power BI Desktop and publish to the Power BI Service. Fabric only comes into the picture when your organization needs heavier data components, or wants a feature that asks for the computing power of a capacity, such as Copilot in Power BI.

Two tools that look alike

At first sight Power BI and Excel do the same thing. You bring data in, you calculate, you make a chart. The way you connect to other data sources looks a lot alike too.

The difference is in what they are good at. Excel is easy. Power BI wins on visualization, and above all on sharing: forwarding an Excel report is and stays awkward.

Ten differences that matter in practice

Data sources: the same engine, a different reach

A misunderstanding that keeps hanging around is that Power Query only sits in Power BI. That is not true. Excel for Windows and Excel for Mac have Power Query built in, under Data and then Get data. It is the same editor and the same formula language M as in Power BI Desktop.

The difference is in the reach. Not every Excel version supports every connector, and Power BI has more of them. On top of that, Power BI also has Power Query Online and dataflows. Then the preparing of your data runs in the cloud instead of on your machine, and several reports can reuse that same prepared data.

When do you choose what

Five roles around the same data

Around data there are usually five kinds of people. In a small company that sits with one or two people, in a large company they are five teams. Knowing who does what mainly helps you know who to go to when you are stuck.

Data analyst

The data analyst turns raw figures into something somebody can base a decision on. They bring the data in, clean it up, build the model and make the reports.

Up front they ask the people who are going to use the report which figures they need. Afterwards they also manage what they built: the reports, the dashboards, the workspaces and the semantic models underneath, with the right permissions on them.

Business analyst

The business analyst knows the company and reads what comes out of the figures. They see a margin dropping and know which customer or which agreement is behind it.

They usually do not build the reports themselves, but they do decide which questions belong in them. So the difference with the data analyst is not in the data, but in what they do with it.

Data engineer

The data engineer makes sure the data is there and stays there. They set up the connections between the company's systems and the place where you get your data, and they keep that flow running.

If your data comes from four systems that do not speak each other's language, they are the one who turns that into a usable whole. As a data analyst you work closely with them, mainly to get to the right sources.

Data scientist

The data scientist looks in the data for patterns nobody thought of up front. Where you show what happened, they try to say what is probably going to happen.

They start from a hunch, test it, and then often knock on the data analyst's door for the visualization and the reporting. So the two roles are closer together than they look.

Database administrator

The database administrator keeps the database standing: available, fast and secure. Backups, recovery after an outage, and who gets which access.

For you they are mainly the person who opens the door to a source you cannot get to yet. They look at the health of the database, not at what is in it.

Install it first

Power BI Desktop is a program you put on your own Windows machine. There are two ways to get it.

Way 1, from the website. Go to powerbi.microsoft.com and under Products choose Power BI Desktop. You get two buttons there. Download free starts right away, but then you do not choose the language yourself. Download with language options does let you choose, and sits a bit lower on the page.

Open powerbi.microsoft.com in a new tab

‹›

1 / 3

Way 2, from the Microsoft Store. Search there for Power BI Desktop and install it from there. Updates then come in automatically, and usually you do not need administrator rights for it.

Then follow the instructions on the screen. After the installation you find Power BI Desktop again through Windows Start, under Recently added.

‹›

1 / 2

You can skip that Sign in button for now

In the top right there is Sign in. You do not need that yet, because for now you work locally. Signing in comes back with the Power BI Service, the cloud version.

If you do want to sign in, you can, but you need a school or work address for it. With a gmail address it does not work.

The interface

The window falls apart into six zones. Take a moment to find them on your own screen, because the rest of the course refers to them all the time.

1. The ribbon. At the top, split into groups of buttons. Which groups you see depends on the tab you have chosen.
2. The report page or the canvas. The large white area in the middle. This is where you build your visualizations.
3. The views, on the left. See below.
4. The page tabs. At the bottom. A report can have several pages. You add, rename and delete them.
5. The Visualizations pane. On the right. This is where you choose the kind of chart and decide which fields go in it. With the little arrow in the top right you collapse it.
6. The Fields pane. Far right. The overview of your tables with their columns, ready to drag to the Visualizations pane or to the filters.

Those views on the left are important enough to set apart. You will switch between them for the whole course.

What you do now

This lesson has a short exercise and then a lot of background. The exercise first. It has three steps, and that is all you need to do now.

Download the practice file (CSV)

If the download does not start, open the file in Google Drive and use the download arrow at the top right there.

‹›

1 / 4

Done. The Power Query Editor that opens now is the subject of the next lesson. Do not close it.

Load or transform

At the bottom right of the preview window is the Load button, with an arrow next to it. Behind that arrow sits Transform data. The choice between those two decides how you work from there on.

The Power Query Editor is a separate tool inside Power BI Desktop. There you prepare your data: remove or add rows and columns, and much more. Only then does the cleaned-up data go into your model.

What you can already see in that preview

Look at the preview window above once more. Power BI has named the columns Column1, Column2, Column3, and the real column names are in the first row. There are also things in there that you do not need: a long column with notes, and at the bottom an empty line plus a source credit from the IMF.

That is normal. Data that is usable right away is rare. You clean that up in the Power Query Editor, and that is exactly what the next two lessons are about.

This is where the doing stops. The rest is just to know.

The exercise is finished. You do not have to carry out what comes next. It is here so you know what exists: other kinds of sources, data straight from the web, an Excel file with several tables, and what happens after you load. It comes back in the final assignments, and then you do it yourself.

Where your data can come from

Click Get data and you see the full list of sources. That list is long, and quite a bit longer than what your version of Excel offers.

If your source is not in the list after all, there is still the generic ODBC connector. That rarely happens.

You are also not limited to one source per project. You can combine a file from a SQL Server database with a file from Excel and a list from SharePoint, all in the same data model.

What Power BI asks you next depends on the source. For SQL it wants the server name, the location and your login details. For an Excel file only the place on your disk.

Getting data from the web, and why that sometimes fails

The technique itself stays useful, so read through it anyway. Type web in the search box of Get data, choose Web and click Connect. A field opens where you paste an address. Power BI fetches that page and looks for the tables in it itself.

‹›

1 / 3

That works for pages that simply send their table along. It fails in two cases: when the table is only built by the browser, and when the owner of the site blocks everything that is not a real visitor. The IMF page from this exercise now falls into that second group.

The Navigator: choosing what you bring in

With a source that holds more than one table, the Navigator opens: a web page with several tables, an Excel workbook with several sheets, or a database. The parts are on the left, on the right you see a preview of what you select. With a single CSV file you do not get that choice, because there is nothing to choose.

If you click Document with a web page, you get a table that is clearly not what you are looking for. So switch from table view to web view. Now you see the page as it looks, split into tables, and you recognize which one you need. Select it in the web view, and on the left it lights up along with it. Switch back to table view and the right data is there.

‹›

1 / 4

A second source: an Excel file

Choose Excel and click Connect. Browse to the place where your files are and select AdventureWorks_Database.

Power BI analyzes the file, looks at what is in it, and opens the Navigator with everything it found.

‹›

1 / 2

You see both named tables and unnamed sheets there. That is confusing, but there is a logic to it. Look at the contents of Sheet2 and you see that it is identical to the table Calendar. Sheet3 is Customer, Sheet4 is Product, and so on.

The reason: if a range in Excel has been given a table name under Table Design, Power BI takes both that name and the sheet into the Navigator. So you see the same thing twice.

What happens after that

Power BI connects to the source and makes a copy of the dataset. Your tables appear on the right in the Fields pane. Your screen does stay empty, because you are still in Report view and you have not put anything on the canvas yet.

‹›

1 / 4

Select a table in the Fields pane and go to Table view. There you see exactly which columns it consists of.

Save and look at the file size

Save your file, through the File menu or with the floppy disk icon. Put it in the same folder as the original Excel files for a moment. That is not necessary, but this way you notice something striking right away.

‹›

1 / 2

The Power BI file is much smaller than the Excel workbook. Surprising, because Excel already saves its files compressed. So Power BI compresses considerably better.

Model view

The third icon on the left is Model view, also called the relationship view. There you see your tables, their fields, and the relationships Power BI has already created itself. A later section comes back to that in detail.

‹›

1 / 2

Another kind of mess: a table made for people

Mess comes in kinds. The second file in the DATA folder is a better example of what you run into for real.

To a person that table does not even look that bad. To Power BI it does. Five things get in the way:

• The colors mean something to you, but nothing to Power BI.
• The top rows are only there to give you extra information.
• The months of 2016 are spread over different columns. Easy to read, useless for building a model with.
• The Grand Total column is unnecessary. Those figures are already in the table and Power BI calculates them itself.
• There are subtotals in between, in various places.

You clean that up in the Power Query Editor, and that is exactly what the next two lessons are.

Four zones

The Power Query Editor is a separate window. Here too you get your bearings first.

1. The ribbon, at the top, with groups and buttons.
2. The queries, on the left. One per table you brought in.
3. The data of the selected query, in the middle, ready to be edited.
4. The query settings, on the right, with the applied steps.

At the bottom left you see how many columns and rows the selected query holds. Handy to keep an eye on while you filter.

Right-clicking a column header

Right-click a column header and a menu appears with the most used operations. You find those same operations in the ribbon too, but right-clicking is faster.

‹›

1 / 2

Feel free to try them out. You cannot break anything, and that is exactly what the next paragraph explains.

Applied steps: you can always go back

Every operation you do is kept as a step in the Applied steps list on the right. You can go back to an earlier step at any moment, change it, delete it, or slide a new one in between.

You change a step with the gear icon next to it. You can also remove steps or change their order. If you want to insert one, first go to the place in the list where you want to be. As soon as you start a new operation there, Power BI warns you that you are about to insert a step.

The steps stay saved, also after you have closed the file and open it again later.

Behind the buttons sits M

The Power Query Editor writes your steps out in a language called M. You can look at that script and, if you have to, change it directly.

The good news: by far the most of it you can just do with the buttons. Learning M is not necessary to follow this course. If you want to know more about it, Microsoft's documentation on Power Query M formulas is ready for you.

Refresh: running the steps again

If something changes in the data in your source, you do not have to redo anything. Click Refresh and Power BI pulls in all the sources again and runs all your applied steps once more, automatically.

‹›

1 / 2

If even that clicking becomes too much for you, the refresh can also happen automatically. That comes up later in the course.

Where you are heading

The table you pulled from the IMF is usable but messy. In this lesson you get it into shape step by step. Work through the steps in order, because the Applied Steps list builds on itself.

Step 1: decent column names

Your columns are now called Column 1, Column 2, and so on. Not handy. The second row holds the names you want: Country, Subject Descriptor, Units, Scale.

That takes two moves. First you remove the row above it, then you turn the new top row into the column header.

1. Go to the Home tab and choose Remove Rows, then Remove Top Rows. Enter 1 and click OK.
2. Still on Home: click Use First Row as Headers.

‹›

1 / 3

Your columns now have a meaningful name. You are not done yet.

Step 2: removing columns you do not need

Use the arrows on the left and right to scroll through the columns. Country, Subject Descriptor, Units and Scale can stay. The column with the country-specific notes cannot. Select it and click Home, then Remove Columns.

Check the rest as well. The years stay, the next step comes back to those, but Column 14 can go.

Step 3: the year columns into rows

Every year now sits in its own column. That reads nicely in Excel, but it does not work for a data model. You want two columns: one with the year, and one with the matching GDP.

Select the first year column, hold Shift and click the last year column. Go to the Transform tab, not Home, and click Unpivot Columns.

‹›

1 / 2

You now have one row per country and per year, instead of a wide table with a column per year.

Step 4: renaming the two new columns

Those two new columns are called Attribute and Value. Double-click the header and type the right name.

• Attribute becomes Year
• Value becomes Gross national product, or GDP if you want to keep it short

Step 5: filtering out empty values

You only want real numbers in your model. Click the arrow next to the header of the GDP column and scroll down to see whether there are n/a values in there. If you find any, uncheck them. In this dataset there are none.

You can also use the Remove Empty filter. The result is the same: empty fields do not go along into your data model.

Step 6: fixing the data type

Click the ABC icon in the header of the GDP column. It says Text. That is wrong, because these are numbers. Choose another type from the list, for example Decimal number. The icon next to the name changes along with it.

If you want to handle several columns at once, select them with Shift and click Detect Data Type on the Transform tab.

For Country, Subject Descriptor, Units and Scale nothing changes, and that is correct: that really is text. For Year the type does change, to Whole number. For now that is fine.

The data types in a row

A data type is the kind of data that sits in a column. By assigning the right type to every column, you keep the data consistent from record to record. These are the common types in Power BI.

Step 7: close and apply

Your preparation is done. Go back to the Home tab and click Close & Apply. That sends the cleaned-up data into your data model and you can start visualizing.

The errors that show up next

First you get a yellow message saying there are pending changes. Do nothing with it. Let the application do its work and be patient.

‹›

1 / 2

Once everything is processed, Power BI reports that there are errors in one of your queries. Click View errors. The Power Query Editor opens, often behind your visualization screen, and on the left there is a new folder Query Errors. Click error in the GDP column there and you see the exact error.

‹›

1 / 2

The problem: the conversion from text to number gets stuck on a comma. That comma only shows up for countries with a GDP above 999,999 dollars. Replace it in the whole column and the error is gone.

1. Select your query again on the left.
2. Go one step back in Applied Steps, so that the type conversion is undone.
3. Right-click the column header and choose Replace Values.
4. Click Insert. An extra step is added.
5. Enter the comma as the value to replace. Leave the other field empty, or put a period in it.
6. Go back to the last step and click Close & Apply again.

‹›

1 / 4

The colored bars under your column headers

Under every column header runs a thin bar. Sometimes fully green, sometimes partly brown, sometimes partly red. That is not decoration.

Turn the analysis on via the View tab, group Data Preview, and check Column quality, Column distribution and Column profile.

Column quality

• The brown part is the percentage of empty fields.
• The red part is the percentage of errors.

Column distribution

• BevragingsID: all values are unique, so all of them are distinct.
• Workshop: twelve distinct values, one of which occurs only once.
• Cursist: forty-one distinct participants, thirty-eight of them unique. So some participants took several workshops.

Column profile

In summary: seven rules for clean data

• Make sure the right data types are used.
• Remove unnecessary columns and rows.
• Avoid repeated values.
• Replace numeric columns with measures.
• Reduce cardinality. (comes back in the advanced part)
• Analyze the model metadata. (comes back in the advanced part)
• Summarize data where you can.

Extra: transposing

Not needed for the exercise above, but good to know. Sometimes a table looks perfectly fine to a human, but it sits the wrong way around for automated queries. If you want to try this out, get the file Test-transpose.

‹›

1 / 4

The steps after that:

1. Home tab, Use First Row as Headers. The years now end up in the column headers.
2. Name columns 1 and 2 Region and Category.

The Region column now holds null values. You can fill those in: Transform tab, Fill, Down. Every null is replaced by the value above it. Filling up also works.

‹›

1 / 3

After that you unpivot the columns again, so the years and values end up in two columns once more.

Why not everything fits in one table

A simple database can consist of a single table. In practice you almost always need more: one for products, one for orders, one for customers.

Two words keep coming back there.

• A record is a row. It brings together all the information about one thing or one person. In a table Products, every row holds a product, with the name, the price, the ID and the supplier.
• A field is a column. It is a single kind of information that comes back in every row, for example the name or the price.

‹›

1 / 2

Two principles that everything rests on

From that follow five rules that a good design meets:

• No duplicate data.
• Split into tables per subject.
• Correct and complete data.
• Everything present that the program needs to combine the information from the tables.
• Fitting what you want to do with the data and want to report on.

The design process in eight steps

1. Needs analysis. Determine the purpose of your database. That steers all the steps after it.
2. Finding and organizing the data. Collect everything you want to record, from product name to order number.
3. Splitting into tables. Divide it into main subjects such as Products or Orders. Every subject becomes a table.
4. Turning data into columns. Decide what you keep per table. A table with employees gets Last name and Hire date, for example.
5. Marking primary keys. Choose a column per table that makes every row unique, such as Product ID or Order number.
6. Setting up relationships. Determine how the data in one table relates to the data in another. Add fields or tables where that makes the relationship clearer.
7. Polishing the design. Make the tables, fill in a few sample rows, and check whether you get out what you were looking for.
8. Applying normalization rules. Check the structure and adjust it where needed.

Step 1 in detail: the needs analysis

If you know what the database is for, you make better choices along the way. So look at it from every angle. If you build something for a public library, you have to think about the borrowers as well as the staff.

You gather information in three ways:

• Talk to the people who are going to use the database.
• Look at the existing forms: invoices, timesheets, surveys.
• Dig through the existing systems, the paper ones as well as the digital ones.

Then make a list of what you want to store and of the entities behind it: people, objects, places, events. For example:

That list later becomes your overview of tables and fields.

Once you know which data goes in, where it comes from and what it is for, only then do you start building. Making databases takes method.

Two ways to do that analysis

There are two schools, both named after the person who came up with them.

Top-down: from overall to detail

In a top-down analysis you determine four things: entity types, entities, attributes and keys.

Entity types

Groups about which you keep the same kind of information.

Entities

The individual instances within such a type. Every row describes one: a customer, a book, an order.

Attributes

The characteristics of an entity. An attribute can play three roles.

• Identifying. It makes the object unique. If you know the student number, you know which student it is about.
• Describing. It says something about the entity, for example the date of birth of that student.
• Referring. It creates a link to an entity of another type.

Bottom-up: starting from what is already there

The bottom-up analysis starts from existing information. It prevents three problems: redundant data, contradictions and inefficiency.

In doing that you distinguish three kinds of data:

The method itself is easiest to see on an order form. You pull all the data out of it, put it in a table, and split that up step by step.

‹›

1 / 5

You keep splitting as long as you can, lay down the links between primary and foreign keys, and give the entities their name.

Repeating groups are a warning sign

Look at the example below. Every student there has Klasse1, Klasse2 and Klasse3 as separate columns. Repeating fields like that almost always point to a design problem.

‹›

1 / 2

If you put those classes in a separate table, student 1022 appears three times, but the column Klasse only exists once. That is exactly the point.

This lesson in short

A minute and a half that makes the rest of this lesson a lot easier: what a key is, how two tables hang together, and how you can tell that you forgot a relationship.

Why the model matters more than the visual

Building your model matters more than the visualization. Without a good model you get no good analysis, no good visualization and no good report. And so you do not draw the right conclusions and you do not make the right decisions.

Time to look at the magic of relationships.

Primary key

The primary key is the attribute that makes a row unique. Without a primary key a table is not a relational table.

Think of an address. If the rows in your table are books, the primary key is the ISBN number.

Three conditions apply:

• The values have to be unique. Watch out: this is about the data in the rows, not about the column names.
• Every table has exactly one, and specifying it is mandatory. It may consist of several columns.
• No value may be empty or null.

Foreign key

Foreign keys exist to prevent duplicate data. They refer to attributes of another entity.

A foreign key is a column, or a set of columns, that refers to the primary key in another table. You do not have to configure or mark anything special for that when you create your database.

‹›

1 / 2

Related data

Entity types can have relationships with other entity types. That is how a network of links between your tables comes about.

Sometimes you need an extra entity, just to link two tables together.

The ERD: your model on paper

An entity is the person, the place or the thing you collect data about. An entity relationship diagram or ERD draws the relationships between those entity types and their attributes graphically. It is an abstract description of your data model. You see the same principle in UML.

Four kinds of relationships

‹›

1 / 4

Two kinds of tables in your model

Go to your relationship view and you see the tables with the relationships Power BI already created by itself.

You can switch that automatic creation off: File, then Options and settings, then Options. There, under CURRENT FILE, go to Data Load and clear the checkbox for automatically detecting new relationships after data is loaded.

You remove the relationships that are already there by moving the mouse over the line until it lights up yellow, then right-clicking and choosing Delete.

Then arrange your tables deliberately: the data tables at the bottom, the lookup tables at the top. Sticking to that pattern consistently saves you a lot of searching later. The star schema is often used for this too.

Lookup tables, also called dimension tables

They answer the questions who, what, where, when and possibly how.

• Who bought: Customer
• What he bought: Product
• Where he bought it: Territories
• When he bought: Calendar

‹›

1 / 2

They usually have few rows but are wide in columns. The number of products is limited, a year always has twelve months, and the number of provinces in Belgium does not change. With a hundred thousand transactions you have rarely served a hundred thousand different customers.

Data tables, also called fact tables

These record transactions and can get enormously big, up to millions of rows. What you mostly find in them is numbers Power BI can calculate with. The same value may appear in them more than once: a product is bought by several customers.

Why a missing relationship is so treacherous

Your data is spread over several tables. The customer is in Customer, his purchases are in Sales. Without a relationship between those two you get no error message. You get a chart that looks normal and that is wrong.

‹›

1 / 3

Creating a relationship with Manage relationships

1. Go to the Modeling tab, Relationships group, and click Manage relationships.
2. Click New in the window that opens.
3. Choose Product in the first dropdown and Sales in the second.

‹›

1 / 2

If both tables have a column with the same name and the same data type, Power BI recognizes it by itself and gives it a gray background.

Two settings at the bottom deserve your attention.

Click OK and then Close. Go to your model view to see the result.

The faster way: dragging

In the Product table, click ProductKey, hold the mouse button down and drag to ProductKey in the Sales table.

A line with a direction appears. Power BI works out by itself which one is the lookup table and which one is the data table. If everything is right, the direction points from the lookup table to the data table.

On one side there is a 1, on the other side a star. That is the one-to-many relationship: a customer has a unique CustomerKey, but that key appears more than once in Sales, because that customer bought more than once.

You create the rest the same way:

• CustomerKey from Customer to CustomerKey in Sales
• SalesTerritoryKey from Territories to SalesTerritoryKey in Sales
• Date from Calendar to OrderDate in Sales

And then the Budget table, which you can combine with all the attributes of Product and Calendar:

• ProductKey from Product to ProductKey in Budget
• Month from Budget to Date in Calendar

What you do in this lesson

You get a flat table with 675,368 sales transactions and you build a star schema out of it yourself. Everything you saw in theory in the previous two lessons, you do for real here.

1. Download the file Sales.csv and save it locally.
2. Open a new Power BI document and set the regional settings correctly.
3. Bring the file in and go to the Power Query Editor.
4. Look at which dimensions you can distinguish in this table.

‹›

1 / 3

From flat to star

This table is very flat, but there really are dimension tables hidden in it. Dimension tables, also called lookup tables, contain the descriptive information that answers who, what, where, when, why and how.

They contain no duplicates. That is exactly why you can always create a one-to-many relationship from them to the fact table.

Put those dimension tables around your fact table and a star appears. That makes your model scalable and easy to read, and it makes your DAX formulas a lot simpler.

Which dimensions do you see? There are six in this table: Product, Date, Geography, Manufacturer, Customer and Campaign.

The conceptual model

Step 1: getting the fact table ready

1. Name your table FactSales.
2. Go to Applied Steps and look at what Power BI already did for you unasked, and whether that is right.

Promoted Headers is fine. But it turned ZipCode into a number, of the type Int64.Type. That is wrong: zip codes that start with a zero lose that zero.

Change that type to Text. To do that, click the 123 icon in the column header while you are on the Changed Type step. Power BI asks whether it should replace the step or add a new one. Choose Replace current.

‹›

1 / 3

Then make three duplicates of FactSales. You build your dimension tables out of those. You focus on DimProduct, DimCustomer and DimGeography, and give the copies those names.

‹›

1 / 2

Step 2: DimProduct

1. Select the DimProduct query.
2. Go to the Home tab, Manage Columns group, and click Choose Columns.
3. Keep ProductID, Product, Category, Segment, Unit Cost and Unit Price.

‹›

1 / 2

Now you only have columns left that have to do with products. But look at your column profile: there is still one row per sales transaction, so still 675,368 rows. In a dimension table you want unique values.

The column distribution shows that ProductID has 212 distinct values. Rule of thumb: the column with the most distinct values is usually the column you filter on for unique values.

‹›

1 / 2

Remove the duplicates: select the ProductID column, or all the columns if you are not sure what the unique identifier is, right-click the column header and choose Remove Duplicates.

212 rows are left: the 212 distinct products. Every value in ProductID is unique now, so every product can be identified by its ID. In FactSales that same ProductID appears more than once, so you can create a one-to-many relationship there.

Step 3: DimCustomer

The same move, with a few extra operations.

Here choose only CustomerId, Email Name and ZipCode. You keep the other address data separate, so that later, when your model grows with the manufacturers for example, everything can refer to the same address table: DimGeography.

Remove the duplicates. 282,596 customers are left.

You split the Email Name column into three columns. Right-click the header and choose Split Column, then By Delimiter.

1. Choose Custom and enter the colon as the delimiter.
2. Repeat to separate the first name and the last name, now with the comma.
3. Name the three columns Email, FirstName and LastName.
4. In the Email column, replace the brackets with nothing. Right-click the column header, then Replace Values.

‹›

1 / 5

Step 4: DimGeography

Quick work, because you know the method by now.

1. Choose the columns you want to keep in this dimension.
2. Remove the duplicates.

‹›

1 / 2

Step 5: DimDate with a script

Here you make a date table with a script. The individual steps come up later in the course. What you use here is the M formula language, which you use to build queries in the Power Query Editor.

‹›

1 / 2

1. Right-click in the sidebar with the queries and choose New Query, then Blank Query. You can also do it with New Source, then Blank Query.
2. Open the Advanced Editor on the ribbon.
3. Paste the code below. Everything that is already there can go.

‹›

1 / 2

//Create Date Dimension
(StartDate as date, EndDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), Date.Day(StartDate)),
EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), Date.Day(EndDate)),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount, #duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, "Year", each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, "Month Number", each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , "Month", each Date.ToText([Date],"MMMM")),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , "Day of Week", each Date.ToText([Date],"dddd"))
in
DayOfWeek

The function is created. You supply a start date and an end date and click Invoke. The table is made.

Name the table DimDate and set the data types of the columns correctly.

Step 6: cleaning up and applying

Your four dimension tables are in place. Now you can remove from FactSales the columns you no longer need there, because from now on they live in the dimensions.

Click Close & Apply. After that you can build your star schema in the model view.

Save the file and close it. You can pick this up again later without any trouble.

Where this comes from

Suppose you build a bar chart with BudgetAmount and SalesAmount on the x-axis, and Category, SubCategory and ProductName on the y-axis.

Subcategory and product name do not seem to be visible. They really are in there, though. Select the chart and you see that symbols appeared at the top the moment you added those fields. With those you drill down through the levels.

‹›

1 / 4

The hierarchy Power BI makes itself

Look at the Date column in your Calendar table. Power BI automatically hung a hierarchy on it: Year, Quarter, Month and Day.

If you do not want that, for example because you build your own date table, you turn it off: File, Options and settings, Options, CURRENT FILE, Data Load, and clear Time intelligence.

Making your own hierarchy

You put category, subcategory and product name together in one hierarchy.

1. Right-click the Category column in the Product table.
2. Choose Create hierarchy.

‹›

1 / 2

Add SubCategory to it: right-click SubCategory, choose Add to hierarchy, and pick the right hierarchy.

The same step through the Properties pane

You add ProductName in a different way, so you know both routes.

1. Go to Model view.
2. Select the hierarchy in the fields of the Product table.
3. Open the Properties pane.
4. Give the hierarchy the name Products.
5. Scroll to Hierarchy and choose ProductName in the dropdown under Select a column to add level.

Display folders: order in your field list

From Model view you can also put your columns into folders. Handy as soon as a table has a lot of columns.

Select the columns that belong together, use Ctrl to pick several at once, and type a name in the Display folder field in the Properties pane.

‹›

1 / 2

What DAX is

DAX stands for Data Analysis Expressions. You use it to add calculations and analysis to your reports.

The big difference with Excel: there you refer to cells in a formula. In DAX you refer to tables, so to rows and columns. You never write "the value in B7", you always write "the SalesAmount column in the Sales table".

You already use DAX without knowing it

Look at the table with Year, Month and SalesAmount on your report page. There is already DAX behind it.

Click the arrow next to SalesAmount in the Values box and you see that Sum is selected. Switch it to Average and the numbers change right away. Power BI made that calculation itself.

An automatic calculation like that is called an implicit measure.

Making an explicit measure

You replace the implicit measure on SalesAmount with a measure of your own.

1. Go to the Modeling tab, group Calculations, and click New measure.
2. A new tab appears at the top, Measure tools, and the formula bar opens.
3. Replace the word Measure with Sales. That is the name the result gets.
4. Type SUM after the equals sign. Power BI makes suggestions. If you then type amount, two options are left. Choose SalesAmount.
5. Check that all brackets are closed and press Enter.

‹›

1 / 2

Sales = SUM(Sales[SalesAmount])

The Sales table now holds Sales, with a little calculator in front of it. That icon means: this is a measure.

Drag it to the Values box and the result is the same as with SalesAmount, apart from the formatting. You simply adjust the dollar sign and the number of decimals.

Why you do this, in three words: control, reuse, and the impact on reports that hang off your model.

Keep an eye on the file size along the way

Save your file now and look at how big it is. When all the data was loaded in, it was 1,989 KB. Now it is at 2,014 KB.

Remember that number. Later on you look again, and then you see something essential about how Power BI works.

And then the calculated column

Next to New measure there was also New column. You use that feature sparingly, and later in this lesson it becomes clear why.

Right-click the Sales table in your fields and choose New column. In Table view you see the columns SalesAmount and TaxAmt at the bottom. The formula box appears again, now with Column filled in instead of Measure.

‹›

1 / 2

Total Sales Amount = Sales[SalesAmount] + Sales[TaxAmt]

After Enter the calculated values are in the column.

Save again now and look at the file size again. It has gone up.

The error that explains everything

Could that calculated column not just have been a measure? Copy the formula, choose New measure this time, and call it Total Sales to avoid confusion.

You immediately see a red underline and an error message.

The message says that no single value for SalesAmount can be determined. That is because you refer to columns with different values. The formula was correct. So why does it not work?

Wrap the calculation in a SUM and it does work:

Total Sales = SUM(Sales[SalesAmount]) + SUM(Sales[TaxAmt])

And you already know the first half of that: it is the Sales measure you made earlier. So you can reuse it:

Total Sales = Sales[Sales] + SUM(Sales[TaxAmt])

This Total Sales is not stored anywhere. It is only calculated the moment a visual needs it. That is why your file grew by only 1 KB, because only the formula is kept.

If you now replace SalesAmount and Sales in your visual with Total Sales Amount and Total Sales, you see exactly the same numbers. But your file is barely heavier.

‹›

1 / 2

From here on, the word measure in this course always means an explicit measure.

First, something to keep at hand

What a hybrid measure is

A hybrid measure combines data from different data tables. Here you work with a measure from Sales and a measure from Budget.

Variance: the difference between two measures

Your table now has Sales and Budget next to each other. You can read the difference with the naked eye, but you can also turn it into a measure.

Variance = Sales[Sales] - Budget[Budget]

You could also have taken both explicit measures together in one go, but then you have to type the whole formula again every time:

Variance = SUM(Sales[SalesAmount]) - SUM(Budget[BudgetAmount])

Set the formatting to Currency, rounded.

Variance as a percentage, and the trap in it

You want that difference as a percentage too. The obvious formula is:

Variance% = [Variance] / [Budget]

But there is a problem in it. If the budget is zero, you divide by zero, and that is not possible. So use DIVIDE, which handles that case neatly.

Variance% = DIVIDE([Variance]; [Budget])

‹›

1 / 3

This approach is ideal. Once you have defined it, you use it everywhere, on all your data tables. You can combine Variance right away with Product and Category or SubCategory, for example.

Hybrid measures work on all your data tables, and straight across your whole model.

The problem: where is my measure?

By now you have a number of measures. You recognize them by the little calculator in front of the name. A measure that stays inside one table, you find back under that table. No problem.

But where does a hybrid measure end up, one that combines two tables? For one person it sits under Budget, for another under Sales. Power BI chooses by itself, and not always the same way.

With a small dataset that is manageable. With a big model and many hybrid measures you are searching all the time. Anyone who has worked with Power BI for a while recognizes this right away.

The solution: your own Measures table

You create an empty table whose only purpose is to hold your measures.

1. On the Home tab click Enter data.
2. A window opens. Give the table the name Measures.
3. Click Load. The table appears with your other tables in the Fields pane.

‹›

1 / 3

Moving your measures

Go through all your tables looking for measures. Under Budget there is at least the explicit measure Budget.

Select it. Watch out: not by ticking the checkbox, but simply by standing on it. The Measure tools tab then becomes active by itself.

On the far left there is a little house with the text Home table. It says Budget right now. Set that to Measures and the measure moves.

‹›

1 / 2

In your Visualizations pane you still have to click Fix this to update the visual in which Budget was used.

Do that for all your measures. The Column1 column that stayed behind in the empty table, you hide through the three dots after the name.

What this does to your model

If you now select a chart that uses Variance, you see in the Fields pane that it holds data from Product and from Measures.

In your Model view the new table is there too. It is not attached to any other table, and it does not need to be. That connection was already made the moment you wrote the measures themselves.

A title that moves along

If you drill one level deeper into the hierarchy in a chart, the title does change to Budget and Sales by Category, but not to which category you are looking at exactly. Typing a fixed title does not solve that, because then it is completely static.

‹›

1 / 4

The solution is a measure that builds the title.

1. Click New measure. Make it in the Measures table right away.
2. Call it ChartTitle.
3. With Shift and Enter you start a new line in the formula bar.

ChartTitle =
var ProductCategorie = SELECTEDVALUE('Product'[Category], "All products")
return
"Sales and Budget for " & ProductCategorie

SELECTEDVALUE returns the selected category, and falls back to "All products" if nothing specific is selected.

Then go to the Title section under the Format tab, the paint roller, and click the fx. There you link your measure to the title.

‹›

1 / 2

Measures Power BI writes for you

Besides measures you type yourself, Power BI has a set of ready-made calculations: the quick measures.

The last measure you made was Variance% with DIVIDE. You can get that same result through a quick measure too.

‹›

1 / 2

1. First choose the operation. In this case a division, which sits under Mathematical operations.
2. Drag Variance to the Numerator field and Budget to the Denominator field.
3. Click OK.

‹›

1 / 2

The measure Variance divided by Budget is added. Look at the DAX formula that appears underneath: it is identical to what you typed yourself earlier.

‹›

1 / 2

Running totals

A running total adds up as the year goes on: January, then January plus February, and so on. TOTALYTD exists for that, where YTD stands for year to date.

‹›

1 / 2

SaleCum = TOTALYTD([Sales], 'Calendar'[Date])

The function needs two things: the measure you want to add up, and the date column it should run along. That date column comes from your calendar table, not from your sales table.

Two dates, one calendar

Zoom in on Sales and Calendar in your Model view. There is a relationship between Calendar[Date] and Sales[OrderDate].

But Sales also has a [ShipDate]. What happens if you create a relationship to that one as well?

What does that mean when you start visualizing? You can show Sales by date and drill down to quarter, month or day, but your sales always hang on OrderDate. ShipDate does nothing.

You can switch relationships, but it solves nothing

You can make the ShipDate relationship active: right-click the relationship, choose Properties, and check Make this relationship active at the bottom.

But this is not the solution if you want to see both values side by side, or together in one visual. Then you need to do something else.

USERELATIONSHIP: waking up the sleeping relationship

Rename your measure Sales to OrderDateSales, and create a new measure ShipDateSales.

ShipDateSales = CALCULATE(
    SUM(Sales[SalesAmount]),
    USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date])
)

USERELATIONSHIP says: for this one calculation, use that inactive relationship. Outside this measure nothing changes in your model.

The scalable solution: a calculation group

For this you need an external tool. The External Tools tab is probably not in your ribbon. Tabular Editor fills that gap. With it you manage all your other measures from one measure.

There is a free version. After installing it you have to restart Power BI before you see the tab.

First delete the extra measures you made with USERELATIONSHIP. You do not need them anymore.

1. Open Tabular Editor.
2. Right-click Model, choose Create New, then Calculation Group. The new group appears with your tables.
3. Name it MeasuresByShipDate.
4. Right-click Calculation Items and create a New Calculation Item.
5. Name the first item ActiveRelationship and give it the function SELECTEDMEASURE().
6. Create a second item and name it ByShipDate.

‹›

1 / 5

Give ByShipDate this code:

CALCULATE(
    SELECTEDMEASURE(),
    USERELATIONSHIP('Sales'[ShipDate], 'Calendar'[Date])
)

Click Save. You get a message that a new calculation group has to be refreshed manually.

‹›

1 / 3

Looking at the result

1. Add a matrix visual to your report page.
2. Put Month from Calendar in the Rows box.
3. Put the Name column in the Columns box.
4. Put Total Sales and Sale Cum in the Values box.

‹›

1 / 2

When you build your own date table

Your queries already contain a Calendar, with plenty of usable columns. Such an extensive calendar table is a luxury, but not always necessary.

The opposite happens too: your queries do contain dates, but there is no Dates, Calendar or Datums table anywhere. Then you build one yourself.

If you do have to build one yourself, there are two routes: with DAX, or with Power Query and M.

Route 1: with DAX

Two functions build a date table.

Go to the Table tools tab, Calculations group, and click New table.

Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

The new table sits with your other tables, with a small calculator icon in front of it. A structure has been added right away too, so you can visualize by year, quarter, month or day.

Do you want extra columns, for example just the year or the week number? Go to the Column tools tab, Calculations group, and click New column.

Year = YEAR(Dates[Date])
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")

Route 2: with Power Query and M

Go to the Power Query Editor, right-click in the sidebar with the queries and choose New Query, then Blank Query.

= List.Dates(#date(2011,05,31), 365*12, #duration(1,0,0,0))

‹›

1 / 2

You now have a list of dates, but you need a table. Go to the Transform tab, Convert group, and click To Table. Then set the data type of the column to Date.

Adding columns goes through the ribbon here instead of through formulas: Add Column tab, Date & Time group, Date button, then Month.

‹›

1 / 2

Marking the table as a date table

You now have three tables with dates. Which fields you need depends on your dataset, and you can always add columns later.

One thing you still have to tell Power BI: which table it should treat as the date table. Right-click the table and choose Mark as date table.

‹›

1 / 2

Extra: the profile of a table

With a query you can build a table that shows the minimum and maximum value per column, plus average, standard deviation, count, number of empty values and number of distinct values.

1. Go to the Power Query Editor with Transform data.
2. Create a blank query: right-click, New Query, Blank Query.
3. Give the query the formula =Table.Profile(Sales), where Sales is the table you want to profile.
4. Give the query a meaningful name, for example SalesProfile.
5. Check the data types and look at the handiest way to order the data.
6. Click Close & Apply.

‹›

1 / 3

You can then simply visualize that data too.

Only now do we get to the picture

You have spent a long time in the back kitchen of Power BI, first with the Power Query Editor, then with relationships and DAX formulas. And only now do you start visualizing.

Was Power BI not about visualization then? That is exactly where the misconception sits. The real power is under the hood, in those two engines: the Power Query Editor and the model. Eighty percent of the work happens there. The other twenty percent is visualization.

Back to Report view

The interface changes, because you are now in your actual data model. This is Report view, where you build your visualizations. On the left you see that the report icon is selected.

Before you start, take a quick look at Table view. That shows your data as it is, column by column.

Tell Power BI that these are countries

The Country column contains countries. Power BI can do a lot with that, but it has to know.

Select the Country column. The Column tools tab becomes active and Data category appears there. It is set to Uncategorized now. Set it to Country.

It looks like nothing changes. It does: in your Fields pane there is now a small globe next to Country.

Building your first visual

Back to Report view, the first icon on the left. Pick a type in the Visualizations pane on the right by clicking it. Start with a stacked bar chart.

A tile appears on your canvas. That is one of the building blocks from lesson 1. You can move it and resize it.

‹›

1 / 2

There is nothing in it yet, because no data is connected to it. Drag GDP from the Fields pane to the Values box.

GDP is on the y-axis now, but that is not yet what you are looking for. What is missing are the years. Drag Year to the Axis box.

And the countries. Drag Country to the Legend box.

Too much information: filtering to the top ten

It is a chart now, but there is too much in it. You only want the ten countries with the highest GDP.

1. Keep the visual selected and open the Filters pane. Country now shows (All).
2. Click the arrow next to Country. For the filter type, choose Top N.
3. Enter the number: 10.
4. Drag GDP to the By value box, because the top ten has to be based on that.
5. Click Apply filter.

‹›

1 / 4

From bar chart to world map

You just told Power BI that Country contains countries. So there is a better way to show this.

Choosing another type is simple: keep the visual selected and click another icon in the Visualizations pane. Choose the map.

‹›

1 / 2

The legend still shows the years. If you only want the total across all years, just uncheck Year.

You add the country names in the Format pane, by sliding the Category toggle to ON.

Building a table and formatting it properly

Now to the bigger model with Sales and Budget. First add a table visual and drag SalesAmount from Sales to the Values box.

‹›

1 / 2

It does not say which currency this is, and you do not want to see the decimals. Three changes.

Bigger font

Click the Format button and go to Grid. Text size is there. Faster: type size in the search box of the Format pane, and Text size pops up right away.

‹›

1 / 3

Currency

Select SalesAmount in the Fields pane. Column tools opens at the top. Set Format to Currency. Click the dollar sign to pick another symbol.

‹›

1 / 2

Decimals

In the same place you set the number of decimals to 0 instead of Auto.

The power of relationships, in a picture

Go to your Calendar table in the Fields pane and drag the Year column to the Values box of your report table.

Under Year a number appears that is not a year at all. Power BI added the years together.

Then copy the table, replace SalesAmount with BudgetAmount, and set the currency and the decimals right there too.

For Budget you see the same figure for every year. That is because there is only budget data for 2016.

Filtering at page level

You only want to show 2016. Open the Filters pane. There you can filter for this one page or for all pages. Choose this page here.

1. Drag Year to the Add data fields here box.
2. Choose Basic filtering and select 2016.

‹›

1 / 2

Months in the right order

Select your table with SalesAmount and add the Month field to the value fields.

The order is wrong. The data is sorted by SalesAmount. If you sort by month, the months end up in alphabetical order, and that is even worse.

The solution: select Month in the Fields pane. Column tools becomes active. At Sort by column, choose MonthNum.

Build BudgetAmount the same way. There the order is right straight away, because the link is in place now.

Adding text and images

Besides visualizations you can also put plain text and images on your report page.

A text box

Choose Text box, type the name of your company, and pick the font, the size and the color.

A logo

Go to the Insert tab. The button to insert an image is all the way on the right. You find a logo in the shared Lesmateriaal folder, or you get one yourself.

Once the image is loaded and selected, a new pane appears on the right, Format image, where you can adjust all kinds of things.

A background

Make sure no element is selected and click the format icon under the visualizations. Go to Page background and add an image. Play with the transparency and with Image fit.

‹›

1 / 2

Card visuals for your key figures

A card shows exactly one value, big and readable. Ideal for the figures that have to stand out right away.

‹›

1 / 2

Copy that card and put a different field in each copy.

The values are in millions now, with an M behind them. If you want the full number, go to the Format tab, Data label section, and set Display units to None instead of Auto. Adjust the text size at the same time so the whole number fits.

Making the figures stand out

Add a shape behind your cards. Give it a color, a border if you want, and send it to the back.

‹›

1 / 3

What happens by default

If you select an item in a chart, the other visuals on the page adjust and show the data for that same item.

‹›

1 / 2

You can change that default behavior, and you need to if you want full control over what the reader sees.

Edit interactions

1. Select a visualization.
2. Go to the Format tab, Interactions group, and click Edit interactions.

The button turns gray, which means it is on. Three icons now appear above every other visualization on the page.

The bold icon is the active one. You can set per visual how it reacts.

‹›

1 / 2

Do you want to turn that off by default for the whole file: File, Options and settings, Query reduction, and there disable cross-highlighting and cross-filtering by default.

Drillthrough: clicking through to a detail page

With drillthrough you build a page that zooms in on one thing: a product, a category, a region. That page opens from a visual on another page, and shows only data for the item you clicked there.

This is how you build one for product category:

1. Create a new page in your report and name it Detail page.
2. Put a visual on it for the entity you want to dig into, for example a card with the top products per country.
3. Drag the field you want to drill through on, here ProductName, to the drillthrough filters.
4. Set the Keep all filters option to ON, so the same filters carry over to the detail page.

Power BI automatically puts a Back button on the page, so the reader can get back to where they came from. You can move that button, make it bigger or replace it with a button of your own.

This is how you use it: right-click a data point in a visual on another page, choose Drill through, and then your detail page.

Tooltips: extra context when you point at something

A tooltip is the little box that appears when the reader moves the mouse over a data point. By default it shows the value and the category, but you can extend that.

Just drag a field from the Fields pane to the Tooltips box. The example below shows Budget added to a chart with Country and SalesAmount.

A tooltip with a whole chart in it

You can also put a full visual in a tooltip. That takes five steps.

1. Add a new page to your report.
2. Set the page size to Tooltip.
3. Set the view to Actual size, otherwise you work at the wrong scale.
4. Build your visual on that page, for example Total Sales per month.
5. Turn Tooltip on under Page information in the Format pane.

‹›

1 / 3

Now Power BI still has to know which value this tooltip belongs to. Drag Total Sales to the value field under Tooltip.

Go back to your report page, select a visualization, add Total Sales to the tooltip of that chart, and move your mouse over the chart to see if it does what it should.

Bookmarks and buttons

With bookmarks you show different views on one page. You can also use them to navigate between pages.

The four steps:

1. Open the Bookmarks and Selection panes from the View tab, Show panes group.
2. In Selection, uncheck the eye for the elements you do not want to show in this view.
3. Create a new bookmark.
4. Add a bookmark button and set the action.

The Selection pane

Selection gives you an overview of everything on your page. You can click an element there, rename it, hide it, or group it with other elements.

‹›

1 / 2

Creating a bookmark

Uncheck the visualizations you do not want in this view, arrange the rest on your page, and then click Add in the Bookmarks pane. Give the bookmark a name.

‹›

1 / 3

Attaching a button to it

Go to the Insert tab, Elements group, Buttons, and choose Bookmark. Give the button a text and set which action should happen on a click.

‹›

1 / 3

Visuals from the marketplace

Besides the built-in visuals, the marketplace holds many more, under AppSource. They are made by Microsoft and by Microsoft partners.

Some are certified, others are not. Certified means the visual meets the code requirements of the Power BI team: it has been tested that it has no access to external services or resources, and that it follows safe coding patterns.

Certification is optional, so a visual that is not certified is not necessarily unsafe.

Importing a visual

1. In the Visualizations pane, click the icon with the three dots and choose Get more visuals.
2. In the window that opens, search for and select the visual you want.
3. Click Add. The downloaded visual appears at the bottom, with the standard charts.

‹›

1 / 3

Python in Power BI

Python can come in handy in three ways:

• Making visuals
• Serving as a data source
• Scripting to prepare data

Before you start, a few things need to be installed.

1. Go to python.org and click Downloads. The exe file is downloaded.
2. During the installation, check Add Python to PATH and click install. That also installs IDLE and the package installer pip right away.
3. Open IDLE from the Windows Start menu.
4. See which modules are there: type help("modules") and press Enter.

‹›

1 / 6

Power BI needs two modules that are not included by default: pandas and matplotlib. You install those from the command prompt:

py -m pip install pandas
py -m pip install matplotlib

After that, the built-in visuals also include the option to generate a chart with Python.

‹›

1 / 2

Why a report gets slow

Power BI Desktop can measure how your report behaves: how long each visual takes, and how much time your DAX formulas claim. That tool is called the Performance analyzer.

It measures every action of the reader and records how long it takes to refresh or update visuals. Everything goes into a log, so you see exactly which parts are heavy.

Six things determine how fast your data refreshes. You will recognize them from the lesson on clean data, and this is why they really matter.

• Use the right data types.
• Remove unnecessary columns and rows.
• Avoid repeated values.
• Replace numeric columns with measures.
• Reduce the cardinality.
• Summarize data where you can.

Clearing the caches before you measure

You find the Performance analyzer on the View tab, in the Show panes group.

There are two caches, and you clear both the same way.

Once the caches are empty and your file is open on the blank page, you can start.

Making a recording

1. In the pane on the right, click Start recording. The button turns gray and the stop button becomes clickable.
2. Browse through your report, drag a few slicers, click through once in the category visual.
3. Click Stop recording.

‹›

1 / 2

All actions are now listed, in milliseconds. With the arrow you sort them, so you see right away which DAX formulas took the most time and which visuals loaded the slowest.

Each visual splits into three categories.

While the recording runs you can also click Refresh visuals. Then all visuals on the active page load again and you see how long that whole move takes. You export the log with Export.

‹›

1 / 2

Optimizing: the visuals

If the visuals are the bottleneck, you look for gains with as little loss as possible for the reader.

• The number of visuals per page counts. Fewer visuals is faster. For each visual, ask yourself whether it really adds something. If not, get rid of it. If you still want to show more detail, use drillthrough pages or tooltips instead of another visual on the same page.
• The number of fields per visual counts too. The upper limit is one hundred fields, measures and columns together. A visual with more than one hundred fields loads slowly. And the fuller the page, the less clear the report becomes.

Optimizing: the DAX query

The Performance analyzer points out the problem but does not say how to solve it. You have to work that out yourself, and DAX Studio exists for that.

Choose Copy query to put the formula on your clipboard and paste it into DAX Studio. There you look at the calculation step by step.

In this example you count all products with an order quantity of five or more:

Count Customers = CALCULATE (
    DISTINCTCOUNT ( Order[ProductID] ),
    FILTER ( Order, Order[OrderQty] >= 5 )
)

After analysis you can try other DAX functions. Here FILTER has been replaced by KEEPFILTERS:

Count Customers = CALCULATE (
    DISTINCTCOUNT ( Order[ProductID] ),
    KEEPFILTERS ( Order[OrderQty] >= 5 )
)

That single change was enough to shorten the duration considerably.

Optimizing: the data model

If the measures and visuals are fast but the DAX query is slow, the problem is probably not the visual. Then a measure is poorly written, or something is off in your model.

Relationships

Check that the relationships between your tables are correct and that the cardinality is set correctly.

Columns

Do not load columns you do not need. Preferably handle that at the source, so they never end up in Power BI. If that is not possible, or the data is already in, use the Power Query Editor to go through it column by column.

For each column, ask yourself what it adds. If it adds nothing, remove it. That shrinks your model, shortens the refresh time and improves the performance of the whole report.

Metadata

Metadata is data about your data: column names, data types, formatting, the schema of the database, the report design, when the file was last changed, how often it is refreshed.

Go through it as soon as your data is loaded. That way you find inconsistencies before you start building. In the Power Query Editor you use Remove Columns, Remove Rows, Use First Row as Headers and the like for that.

For the columns themselves you have three tools on the View tab, the same ones you saw in part 2.

• Column quality. What percentage is valid, in error or empty. If valid is not at 100, find out why.
• Column distribution. How many distinct items there are and how many of those are unique. That is how you determine the cardinality of a column.
• Column profile. More statistics plus a chart of the distribution.

Watch the file size itself too. Power BI compresses your data with the VertiPaq engine and stores it on disk. The size of your model works straight through into performance: smaller model, less memory, faster refresh, faster calculations, faster visuals.

The auto date/time feature

This option is on by default. It automatically creates a hidden calculated table for every date column in your model. Those tables come on top of what you already have.

That is handy if you work with calendar periods and your model is simple. But if your source already has a date table, you should use that one, so that time means the same thing across your whole organization. Then turn the automatic option off. That shrinks your model and shortens the refresh time.

Turning it on or off: File, Options and settings, Options. Choose Global to make it apply everywhere, or Current File to do it for this file only. Then go to Data Load and check or uncheck the box at Time intelligence.

Variables: faster, easier to read, easier to fix

The same measure, written twice.

Without a variable

Sales YoY Growth = DIVIDE (
    ( [Sales] - CALCULATE ( [Sales], PARALLELPERIOD ( 'Date'[Date], -12, MONTH ) ) ),
    CALCULATE ( [Sales], PARALLELPERIOD ( 'Date'[Date], -12, MONTH ) )
)

With a variable

Sales YoY Growth =
VAR SalesPriorYear = CALCULATE ( [Sales], PARALLELPERIOD ( 'Date'[Date], -12, MONTH ) )
VAR SalesVariance = DIVIDE ( ( [Sales] - SalesPriorYear ), SalesPriorYear )
RETURN SalesVariance

The first version is inefficient: Power BI has to calculate the same expression twice. The second calculates PARALLELPERIOD only once.

Readability

Use descriptive names. SalesPriorYear says what it is. If you call it X, temp or variable1, nobody will understand later what is going on, you included. Clear names make the report maintainable for whoever comes after you.

Finding errors

With variables you can test step by step. Temporarily rewrite the RETURN to the variable you want to look at. The measure then returns only that value.

Sales YoY Growth % =
VAR SalesPriorYear = CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH))
VAR SalesPriorYear% = DIVIDE(([Sales] - SalesPriorYear), SalesPriorYear)
RETURN SalesPriorYear%

When you are done testing, you just put the RETURN back. That is how you peel a complicated calculation apart layer by layer.

Aggregations: summarizing what you do not need per row

By summarizing data your table gets drastically smaller. You keep what matters and your queries get faster.

If you have access to the database, you create a table or view with the aggregation there and load that in. Otherwise you build it in the Power Query Editor.

Look at your file size first, so that you see the difference later.

In the Sales table the date 01-01-14 appears five times. Those five purchases were made by five different customers, because the CustomerKey differs. So every sales transaction is in there separately.

58,189 transactions in total, each on its own row. You see the exact number in the Column profile on the View tab.

‹›

1 / 2

Building the aggregation

You do not need a view of every transaction. You want to know how many transactions there were per day and what the daily amounts are.

1. Go to the Home tab, Manage Columns group, and click Choose Columns.
2. Uncheck everything except OrderDate, OrderQuantity and SalesAmount. Click OK.
3. Set the data type of OrderDate to Date. No time of day was recorded anyway, so the hour can go.
4. Go to the Home tab, Transform group, and click Group By.
5. Fill in the details and click OK.

‹›

1 / 3

1,073 rows remain, and the results of your aggregations are ready as calculated columns. Click Close & Apply.

Managing aggregations

You can do that from any view in Power BI Desktop. Right-click the Sales table in the Fields pane. In the window that opens you use the dropdown to change which kind of aggregation you use.

‹›

1 / 2

Two icons you have not used yet

At the bottom of the left-hand bar in Power BI Desktop there are two icons you have not come across in this course: DAX and TMDL. You can go through the whole course without touching them, and most people do.

You do not have to use them. But you do need to know they exist, because you will only recognize the moment you need them if you have heard of them.

First, setting one term straight: formula versus query

In part 4 you wrote DAX formulas: measures like Total sales or Margin percentage. A DAX query is something else, and that difference is the key to this whole lesson.

The DAX query view: what a query looks like

A DAX query has two parts. One of them is required.

EVALUATE, required

This says which data you want back. After it you put something that returns a table.

EVALUATE 'Sales'

That gives you the whole Sales table back. If you want to see one value, put it in curly braces. That turns a single number into a table of one row:

EVALUATE { [Total Sales] }

With ORDER BY you sort the result:

EVALUATE 'Sales'
ORDER BY 'Sales'[SalesAmount] DESC

DEFINE, optional

With this you write a measure that exists only inside this query. You can try it out without anything changing in your model.

DEFINE
    MEASURE Sales[Test margin] =
        DIVIDE ( [Total Sales] - [Total Cost], [Total Sales] )
EVALUATE
    SUMMARIZECOLUMNS (
        'Product'[Category],
        "Margin", [Test margin]
    )

That is the whole trick: you test a calculation without building a table visual first, without clicking back and forth, and without polluting your model with measures that turn out to be wrong.

Quick queries: you do not have to know anything by heart

Right-click a table, column or measure in the Data pane and choose Quick queries. Power BI writes the query for you, opens it in a new tab and runs it right away.

From query back to your model

Do you like the measure you tried out in the DEFINE block? Then you do not have to retype it.

Above a DEFINE MEASURE block, clickable text appears, what Microsoft calls CodeLens:

• Update model: Add new measure if the measure is not in your model yet.
• Update model: Overwrite measure if it already exists but the formula differs.

If you want to apply several at once, use the Update model with changes button in the command bar.

Adding a description right away

You can put a description above your measure with three slashes. It goes along to your model when you write it back.

DEFINE
    /// Growth in sales compared with last year, in percent.
    /// Returns blank if there is no figure for last year.
    MEASURE Sales[YoY Growth %] = ...

The rest of the view

Tabs

You can have several queries open at once, each in its own tab that you can rename. Each tab shows its state:

Queries keep running in the background, so you can carry on in another tab meanwhile.

Reading error messages

If something like Query (4, 23) appears, the error is on line 4, character 23. The view helps further with suggestions while you type, and if you hover your mouse over a measure you see the formula, the name and the description.

The bridge from the previous lesson

In the Performance analyzer from lesson 21 you could see which query a slow visual sends. Expand a visual there and you get the option to copy that query or to run it directly in the DAX query view.

With a live connection

If you work with a live connection to a published model, you cannot view or edit the measures of that model. Quick queries are then limited to Evaluate. You can test report measures you add yourself, but those always have to be in a DEFINE MEASURE block, because they do not exist in the model itself.

The TMDL view: your model as text

TMDL stands for Tabular Model Definition Language. It is the language in which the definition of your model is described: your tables, columns, measures, relationships, formatting, everything.

In the model view you see that as a diagram with boxes and lines. In the TMDL view you see the same thing, but as text you can read, search and change.

How you work with it

The view is empty when you open it. You fill it by putting an object into it.

1. Drag a table, measure or column from the Data pane into the editor. Power BI writes out its full definition as a script.
2. Or right-click an object in the Table view and choose Script TMDL, to a new tab or to your clipboard.

You can drag whole sections at once, or select several objects by holding down Ctrl.

This is what such a script looks like:

createOrReplace
      perspective SalesView
        perspectiveTable Sales
            perspectiveMeasure 'Sales Amount'
            perspectiveMeasure 'Sales Qty'
            perspectiveColumn Quantity
            perspectiveColumn 'Amount'

The editor helps you along the way with colors per kind of element, sections you can expand and collapse, completion while you type (or with Ctrl+Space), an explanation when you keep your mouse on something, and a light bulb at an error with a suggestion to fix it.

When you are done, you click Apply. If it works, you get a confirmation. If it does not, an error message appears with a link to the details.

Where it really saves time

This is not an everyday tool. But in these six cases it is the difference between ten minutes and half a day.

What to watch out for

One more subtlety. The link between your model table and your Power Query query runs through the sourceColumn property, which keeps the names independent of each other. So in the Power Query Editor you see the original column names, not the ones from your model.

When to use what

Getting your report out the door

Once your data model is in place and you do not need to change anything about it for now, you can publish. From that moment on you can share with others or keep working together with colleagues.

If you are not signed in yet, Power BI first asks for your email address.

When publishing is done, you get the link to your report. Click it to go to the Power BI Service.

What you can and cannot do in the Service

In the Service the Power Query Editor is gone. You also cannot change the relationships between your tables there. That stays work for Desktop.

Editing does work. Click Edit and the interface changes: a different ribbon, but you still find Fields, Visualizations and Filters just like in Desktop.

‹›

1 / 2

The fifth building block: the dashboard

A dashboard is a single page that tells a story with visuals. Because it stays on that one page, a good dashboard works with key figures. Anyone who wants more detail goes to the full report.

You build a dashboard by pinning visuals from a report with the pin icon.

A window opens with three things: a preview of the chart, the choice to change or keep the theme, and the choice between an existing or a new dashboard. If you pick new, you name it right away.

You can also combine visuals from different reports on one dashboard. That way you put key figures from several departments side by side on one page, whether the data sits in the cloud or locally.

Report or dashboard: what does what

What a workspace is

Workspaces are the central place where you build collections of reports and dashboards with colleagues and teams. They are containers for dashboards, reports, workbooks, datasets and dataflows. They are created on capacities.

Three advantages:

• Keep reports and dashboards for use by several teams.
• Share and present everything from one environment.
• Control who has access to which datasets, reports and dashboards.

Two kinds of workspaces

Think of a workspace as a temporary work area and as a container for what will later become a Power BI app.

Creating a workspace

1. Go to Workspaces in the icons at the bottom. A side pane opens.
2. Click New workspace.
3. Give it a meaningful name and a description. Click Save.

‹›

1 / 2

If you expand Advanced, you can create a contact list of people who get a notification when something goes wrong with the workspace. By default that is the workspace admin.

You can also link the workspace to a specific OneDrive, and choose whether it is part of a dedicated capacity. With a dedicated capacity your workspace gets its own compute power instead of sharing it with other users.

You land straight in your new workspace and can add content and set roles.

Setting up row-level security in Desktop

You can grant permissions on whole workspaces, on reports, datasets, dashboards and workbooks. Even on individual rows. That last one is called row-level security, or RLS.

1. Go to the Modeling tab, Security group, and click Manage roles.
2. Create a role and put filters on the attributes of your entities.

‹›

1 / 2

In this example the role Groep_USA only gets access to the data about the United States within the Territories table. Click Save.

You test with View as, also in the Security group. Pick your new role and see what changes.

‹›

1 / 4

Click Stop viewing to return to the normal view.

Applying row-level security in the Service

The role exists, now you add people to it. They will only see data about the United States.

1. In the Service, click Workspaces on the left and pick the workspace your report is in.
2. You see your dataset and your report. That can be in list form, or through Lineage view if you want to see the whole data flow.
3. Click the three dots next to the dataset and choose Security.
4. Add people or groups. Click Save.

‹›

1 / 6

Here too you can test: through the three dots next to the group name you choose Test as role.

Workspace roles: who is allowed to do what

In your workspace, go to Access. There you add email addresses of individuals, mail-enabled security groups, distribution lists, Microsoft 365 groups or plain security groups.

‹›

1 / 2

There are four roles: Admin, Member, Contributor and Viewer. Each can do something different.

Five footnotes to that table, and they are not minor:

• A contributor may update the app if the admin grants that permission. Publishing a new app or changing who has access to it is not allowed.
• Contributors and viewers can share items if they have reshare permissions.
• To copy a report to another workspace, or to create a report on this dataset there, you need Build permission on the dataset. From the Contributor role up you get that automatically through your workspace role.
• For the gateway you need separate permissions. Those are managed elsewhere, apart from the workspace roles.
• Even without a Pro license you can view and use items in the Service, if they sit in a workspace on a Premium capacity.

Want an overview of everyone in a workspace with their permissions: click the three dots next to the name of the workspace and choose Workspace access.

‹›

1 / 2

Sharing a single report

Open the report and go through the File menu to Manage permissions.

In the window that opens you can, per item, so for dashboards, workbooks, datasets and reports:

• show the link and send it by email
• give direct access to users you add
• approve pending requests
• view shared views

Comments: asking for feedback in the report itself

Once your report is in the Service and you have shared it, you probably want to know what your colleagues think of it. That is what comments are for.

You can place them on a whole dashboard, on a single visual of a dashboard, on a report page, on a paginated report and on a single visual of a report page. General, or aimed at someone with an @mention.

From workspace to app

You have created an app workspace and assigned roles. Now you fill it with content: reports from Desktop, but also dashboards, datasets and dataflows.

An app is a published window onto your data, read-only, meant for wide distribution. Publishing requires a Power BI Pro license.

Adding content to the workspace

Go to your workspace and click New. There you can create a report, a dataset, a dataflow or a streaming dataset, among other things.

Or use Get data at the bottom left of the navigation bar to import existing reports from Power BI Desktop.

Creating the app

Ready to publish? Go back to the workspace and click Create app at the top right.

A window opens with three tabs.

Setup

Give the app a name and a description, and optionally pick a logo or a theme color.

Content

Here you add content and decide in which order the user sees it, through your own navigation pane. You can change names, adjust links and place items in a section.

‹›

1 / 2

You can also add content from outside Power BI through a link. Watch out: a YouTube video or a PowerPoint has to be an embed URL, not the regular URL.

For each item you can turn on or off whether it goes into the app when you publish.

Audience

Here you give access to your whole organization or to specific users. You can also give them permissions to build and share, so they can create and distribute content themselves.

After publishing you get a screen with the link you can forward.

Updating the app

If you want to change something after publishing, you do that in the workspace, not in the app.

One exception: changes to datasets and dataflows are active right away. When you update, you get to the three tabs Setup, Content and Audience again. Then click Update app.

Deployment pipelines

Two kinds of refresh

In Power BI the word refresh covers two different things. Keep them apart.

This lesson is about the second one. The first comes in a later round.

Setting up automatic page refresh in Desktop

1. Select the report page you want to turn it on for. The setting applies per page.
2. In the Visualizations pane, go to the Format button, the paint roller, and look for the Page refresh section at the bottom.
3. Turn the toggle on.
4. Choose the refresh type: fixed interval or change detection.
5. Fill in what that type needs.

Fixed interval

You enter an interval. The default is 30 minutes, the minimum is one second. Your report then refreshes at that frequency.

Click Show details and Power BI shows whether your admin has enabled the feature, what the minimum allowed interval is, what the actual refresh rate is (usually slower than what you set) and when the last refresh happened.

Change detection

With this, Power BI only refreshes when something has really changed. You link a measure that watches for that.

You reach the window through the Add change detection link, through the Modeling tab in the ribbon, or by right-clicking a value in the Values box.

Pick an existing measure or create a new one. For a new one you can choose from count, distinct count, minimum, maximum and sum. For example: count the number of distinct customer IDs, and only refresh when a customer is added. Then decide how often Power BI may recalculate that measure. After Apply there is a new measure in your field list, with its own icon.

Choosing the right interval

As soon as automatic page refresh is on, Power BI keeps sending queries to your source. Those queries need time to come back. If you set the interval shorter than that time, your visuals simply refresh less often than you set.

With very short intervals three things count: the type of DirectQuery source, the load your queries put on it, and the distance from your readers to the data center.

A worked example with the Performance analyzer from part 6:

• Data comes in every 2 seconds.
• The Performance analyzer shows a maximum query plus display time of about 4.9 seconds.
• The source can handle about 1,000 concurrent queries per second.
• You expect 10 concurrent readers.

5 visuals times 10 users gives about 50 queries. The data comes in every 2 seconds, so that should be your refresh rate. But the query takes about 5 seconds, so you have to go higher than 5 seconds.

The same considerations apply to change detection, with one big difference: only one query goes to the source, no matter how many visuals or readers there are. Only when that measure changes are all the visuals updated for all users, and then the same calculation as above applies again. In the long run that is a lot more economical.

Automatic page refresh in the Service

The same principle, with the same steps. In the Service it also works for embedded Power BI content.

What the Service allows you

In Desktop anything goes, down to one second. In the Service it depends on where you publish.

A capacity is a set of resources, storage, processor and memory, that runs Power BI content. Shared capacity is capacity you share with other Microsoft customers, and there limits apply to give everyone equal chances: a maximum model size of 1 GB and a maximum of eight refreshes per day. A reserved capacity is for your organization alone.

Workspaces sit inside capacities. By default they end up in the shared capacity, including your personal one.

Limitations to remember

• The Import storage mode is not supported. Only DirectQuery.
• Composite models with at least one DirectQuery source do work.
• One change detection measure per dataset.
• A maximum of 10 models with change detection per tenant.
• Queries for automatic page refresh always bypass the cache.
• Known issue: with a connection to Analysis Services or Power BI datasets and an interval of 30 minutes or more, visuals can show an error after 30 minutes.

Power BI protects your source in two ways: these queries get a lower priority than interactive queries such as loading a page, and no new refresh query is sent while the previous one is still running.

When it does not work

Embedding a report on a website

You can embed a report in a web page or a portal, so readers see it without going to the Power BI Service.

‹›

1 / 2

The payoff for that eighty percent

Part 5 already said it: eighty percent of your work in Power BI sits under the hood, in the Power Query Editor and the model. Twenty percent is visualization. That may have looked like an old-fashioned message in a time when you can just ask a computer for a report.

So this lesson is about two things. What Copilot can do for you, and what you have to do to make that work.

First the annoying question: are you allowed to use it?

Copilot in Power BI is not in every license. This is where a lot of people get stuck, so it comes first.

If the terms capacity and workspace no longer ring a bell, take a quick look back at lesson 23.

Where you run into Copilot

Some features sit in the Copilot pane, others are built into the place where you are already working, like the button next to a measure.

What it can do for the people who read the report

Have a report summarized

Getting to the bottom of a page can take half an hour, sometimes longer, especially if you have to say something about it to management. Copilot turns that into a summary in seconds, with the trends, the insights and the notable outliers.

You can steer the summary with your question: summarize this page in bullet points, or give a summary of the sales on this page.

Asking questions about the data

You ask a question in the Copilot pane and get an answer with a reference to the visual it comes from. That way you see right away which part of the report backs up the answer.

Examples of what you can ask:

• Can I see this measure for a different region or period?
• What are the most important categories based on this value?
• How has this measure changed over time?

What it can do for the people who build the report

Have a report page built

Give Copilot a high-level prompt that fits your data, and it pulls together the tables, fields, measures and charts and puts down a page. After that you just adjust it yourself with the tools from part 5.

Examples of useful prompts:

• Build a page to judge the performance of the different shifts based on approved units, rejected units and alarms over time.
• Build a page to analyze the efficiency of the production line.
• Build a page that compares the cost and the material of each product.

You can change existing pages too: add visuals, pick a different visual type, change fields, delete visuals. And undo just works.

If you do not know where you want to start yet, Copilot can propose a structure for a whole report based on your data: which pages there would be and what they cover. From that you pick what it may build.

Have DAX queries written

Describe what you want to calculate and Copilot proposes a DAX query. If you like it, choose Keep to put it in the query tab, and run it with Run or F5.

If you ask how the code works instead of asking for new code, you get an explanation in plain language. That makes it a learning tool for the formulas from part 4 as well.

Descriptions for your measures

Copilot can add descriptions to the measures in your model. Anyone who later builds a report on your model sees that description. That is your documentation right there, and it is one of the things Copilot itself gets better from too.

A summary as a visual on the page

Besides the pane, you can also put a summary on your report page as a visual, so every reader sees it.

Preparing your model: the real work

This is the heart of the lesson. Microsoft says it bluntly itself: without preparation Copilot can misinterpret your data, and then you get generic, inaccurate or misleading answers.

Five things decide how well your model works with Copilot. You know all of them from part 3.

Prep data for AI

On top of those general rules, Power BI has three tools made specifically for Copilot. You find them under the Prep data for AI button on the Home tab in Desktop, or through the page of your semantic model in the Service.

1. The AI data schema

This is where you decide which part of your model Copilot may see. Uncheck everything that is not relevant to the user: technical key fields, helper columns to sort on, unused tables. The less noise, the sharper the answer.

Two things to know. Fields that are already hidden in your model are excluded automatically on first use. And the schema does not apply everywhere: when building a report page, when searching and for DAX queries, Copilot needs the whole model and ignores your schema.

2. Verified answers

This is where you link an existing visual to a question. Select a visual, use the three dots to choose the option to turn it into a verified answer, and enter the questions it should respond to. If someone asks that question, or something a lot like it, they are guaranteed to get that exact visual.

That way you keep control over the questions that really matter. For the key figures your organization steers on, you do not want a shifting answer.

3. AI instructions

Free text in which you explain to Copilot how to handle your model. For example which of two similar columns is the right one. Use this as a last step, for the fine-tuning, not as a first resort.

And after that: approved for Copilot

If you are happy with it, you can mark your model as approved for Copilot. That is a signal to your organization that this model has been checked.

What you have to tell your users about it

People like to skip this part. Do not, because it decides whether Copilot gains or loses trust in your organization.

Asking the same thing does not always give the same answer

Copilot is non-deterministic. The same question twice on the same data can produce two different answers. Tell your users that up front, and explain how they can check an answer.

Except when it comes from the cache

If you ask exactly the same question within 24 hours on an unchanged model, you get the answer from the cache, so literally the same one. The button to clear the conversation does not change that. If you expect a different answer, phrase your question differently or refresh your model.

It can grab the wrong field

An example from Microsoft's own documentation. Someone asks which country made the highest profit in 2024. Copilot answers that there is no data for 2024, and then filters on the Birthday column from the customer table instead of on the marked date table. There really was data for 2024.

How to check what happened

If Copilot built a visual for you, click the button to add it to the page. After that you can select that visual and see which fields and which filters were used. That way you make visible how it got to its answer.

If you switch topic, clear the conversation. Otherwise Copilot drags the context of your earlier questions along and the answers get muddy.

What it cannot do

• The Q&A switch has to be on for your model, otherwise Copilot cannot build or edit report pages.
• No report pages for models with real-time streaming.
• No report pages for models with a live connection to Analysis Services.
• No report pages if you have turned off implicit measures. The implicit measures from lesson 12, that is.
• No custom visuals from the marketplace.
• No formatting or style changes.
• With complicated visuals, detail or formatting can get lost if you have them changed.

What it comes down to

Copilot does not change what a good report is. It changes how fast you have one, and it mercilessly exposes whether the model underneath is any good.

Why this part is at the back

This part is not about buttons. It gives the context Power BI exists in: where all that data comes from, how it is stored, and why one source behaves differently than another.

You do not need it to build a report. You do need it when you sit down with a data engineer or a database administrator and want to understand what they are talking about.

Big data: the three V's

In 2001, Doug Laney, then at Meta Group and later at Gartner, wrote a report about the possibilities of data. Three V's are central in it: the three ways big data was going to grow explosively.

Volume

Humanity produces more than two quintillion gigabytes a day, and that number keeps growing. We live on smartphones and laptops, and apps with media content drive exponential growth. More volume means more need for new systems and for people who can handle them.

To make that last one imaginable: 175 zettabytes on DVDs would give a stack that reaches 22 times around the earth. And the Amazon cloud uses as much energy as 600,000 households.

Variety

Think of the ways you store data yourself. From Excel to PDF, from video to formats that do not exist yet today. In your own life you easily reach a hundred formats.

Because data comes in so many formats, what you collect is not always structured, and it does not always come from a clear source. Data is pulled from images, music, video, complaint emails and other texts. On unstructured data you first unleash algorithms to get usable data out of it.

Velocity

The speed at which data comes in and gets processed. Yesterday's news used to be important, and newspapers still work that way. News sites and radio have got us used to short and fast.

The data warehouse

A data warehouse, DWH for short, is a type of data management system. It is a collection of data built in such a way that recurring and one-off questions can be answered fast, without the source systems underneath suffering for it.

A typical data warehouse contains:

• A relational database.
• An ETL solution: extract, transform and load. Pulling data from an external source, converting it into a usable format, and loading it into the warehouse.
• Functions for statistical analysis, reporting and data mining.
• Analysis tools to visualize and present.
• Applications that derive usable information with machine learning and artificial intelligence.

Structured versus unstructured

Before you can analyze unstructured data, loose values or series of words have to be turned into columns. Text has to be normalized for that: leveling out upper and lower case, taking out stop words, and so on.

SQL and NoSQL

SQL

SQL stands for Structured Query Language. Traditional databases work with it, and to work with it your database has to have structure.

What you do with SQL:

• Create databases
• Create tables
• Insert and update records
• Query and retrieve data
• Create procedures and define views
• Grant access rights to tables, procedures and views

NoSQL

NoSQL stands for Not only Structured Query Language. It is an umbrella term for very different databases that have little in common with each other. They can process extremely large amounts of data, and that data does not have to have structure up front.

Text mining and NLP

With the arrival of large amounts of unstructured data, the demand grew for ways to analyze it. Text mining and natural language processing pull information out of texts. Words or groups of words are classified as variables, for example to determine or predict the sentiment.

The difficulty is in dealing with frequent and rare words, and with words that have several meanings. All kinds of algorithms have been developed for that.

And then visualization

Besides processing and analyzing data, a leap has also been made in the ways to show it. The time of line and bar charts in Excel seems to be over.

Visualization is more than making overviews to get insight into your data structure. Besides what you see while exploring your data, visualization is increasingly the way you tell a story.

Web scraping

Web scraping is a technique where you pull data from the internet in a controlled way. You build your own table with the columns and rows you find useful.

Practice address: the list of classics in the Xbox store, via the link under the image in the source material.

Column From Examples

This feature is one of the nicest in Power Query. You type an example of what you want, and Power BI works out the rule itself.

First get sample data from Wikipedia.

1. Go to the Home tab, Get data, Web.
2. Paste the address of the Wikipedia page with the list of states and territories of the United States and click OK.
3. In the Navigator, choose the table with the states.
4. Click Transform data. The table opens in the Power Query Editor.
5. Go to the Add Column tab and choose Column From Examples.
6. Click the icon itself to build the column from all existing columns, or use the arrow to choose between From All Columns and From Selection. Take From All Columns here.

‹›

1 / 2

How it works

At the top of the table the Add Column From Examples pane opens. To the right of your existing columns, Column 1 appears. Fill in example values there and Power BI derives rules from them.

If you type Alabama in the first row, Power BI recognizes that as the value Alabama from the first column. Press Enter and it fills in the rest of the column.

Then go to the row Massachusetts[D] and remove the [D] part. Power BI notices the change, derives a new transformation from it, and renames the column to Text Before Delimiter.

Keep giving examples until it is right, then click OK. You rename the column by double-clicking the header, or by right-clicking and Rename.

Which transformations are supported

Many, but not all.

General

• Conditional Column

Reference

• Reference to a specific column, with transformations for trim, clean and capitalization

Text

• Combine, with a mix of fixed text and complete column values
• Replace
• Length
• Extract: first characters, last characters, range, text before a delimiter, text after a delimiter, text between delimiters, length, remove characters, keep characters

Date

• Day, day of week, name of the day of the week, day of year, day of month
• Month, name of the month, days in month
• Quarter of year, week of month, week of year, year
• Age
• Start and end of the year, the month, the quarter, the week and the day

Time

• Hour, minute, second, to local time

Number

• Absolute value, sign
• Add, subtract, multiply, divide, integer-divide, modulo
• Square, cube, square root, exponent, factorial
• Natural logarithm, base-10 logarithm
• Sine, cosine, tangent, arcsine, arccosine, arctangent
• Round down, round up
• Is even, is odd
• Convert to number, sum, ranges

Using R in Power BI

Installing

1. Go to the download page for Microsoft R Open and MKL.
2. Take the Windows version, because that is what Power BI Desktop runs on.
3. Start the installation.

‹›

1 / 4

Activating R in Power BI

Getting data

Go to SampleData.org and choose a dataset. Not all sets work equally well. This example uses the longley set.

‹›

1 / 4

Plotting a chart

Azure integration

Useful links

• The official Microsoft documentation about building reports in Power BI. Watch out: today it lives on learn.microsoft.com. Older addresses with docs.microsoft.com no longer work.
• DAX Studio, for investigating slow queries: daxstudio.org
• Tabular Editor, for calculation groups and model management.
• The interactive DAX report from Microsoft with all functions and how to use them. You find it with the course material.

Q&A

Q

• How do I disconnect a slicer from a particular chart?
• Can I use personalized fonts in PowerBi Desktop?
• What is the difference between a report and a dashboard?
• What is the difference between datasets and workbooks in the PowerBi Service?

A

• How do I disconnect a slicer from a particular chart?
• Select the slicer.
• Go to the ‘Format‘ tab.

• In the ‘Interactions‘ group, click ‘Edit interactions‘: the button is shown with a darker background once you have clicked it, and the following icon becomes visible on each of the charts on the page.

Clicking this icon on a specific chart disconnects it from the selected slicer.

• Can I use personalized fonts in PowerBi Desktop?

Good news, yep, you can! It does take a few steps.

Do know that: this is officially (for now) not a supported feature of PowerBi. Your own fonts are only shown in the service if the font is really installed on the machine of the end user.

• Make a JSON file of your PowerBi theme.
• Make a copy of the file and change the font (fontFamily) to the font you want.
• Choose your new JSON as the theme.
• Make a JSON file of your PowerBi theme.

For your convenience, here is an example JSON file for a LIGHT theme. You can paste this into a notepad, save it, give it another name and adjust it if you want.

{ "name": "Sample Light Theme 1.1", "background": "#ffffff", "backgroundLight": "#ffffff", "backgroundNeutral": "#ffffff", "foreground": "#000000", "foregroundNeutralSecondary": "#535353", "foregroundNeutralTertiary": "#9b9b9b", "tableAccent": "#0c62fb", "dataColors": [ "#0c62fb", "#6da1fd", "#85b0fd", "#c2d8fe", "#9b9b9b", "#6e6e6e", "#3e3e3e", "#efbfbf" ], "good": "#0c62fb", "neutral": "#9b9b9b", "bad": "#efbfbf", "maximum": "#666666", "center": "#9b9b9b", "minimum": "#666666", "null": "#efbfbf", "textClasses": { "callout": { "fontSize": 28, "fontFace": "Segoe UI", "color": "#0c62fb" }, "title": { "fontSize": 14, "fontFace": "Segoe UI", "color": "#0c62fb" }, "largeTitle": { "fontSize": 14, "fontFace": "Segoe UI", "color": "#0c62fb" }, "header": { "fontSize": 12, "fontFace": "Segoe UI", "color": "#535353" }, "label": { "fontSize": 10, "fontFace": "Segoe UI", "color": "#535353" }, "lightLabel": { "color": "#3e3e3e" }, "largeLightLabel": { "color": "#3e3e3e" }, "smallLightLabel": { "color": "#3e3e3e" } }, "visualStyles": { "page": { "*": { "background": [ { "color": { "solid": { "color": "#ededed" } }, "transparency": 0 } ], "outspace": [ { "color": { "solid": { "color": "#ffffff" } }, "transparency": 0 } ], "outspacePane": [ { "backgroundColor": { "solid": { "color": "#ededed" } }, "foregroundColor": { "solid": { "color": "#3e3e3e" } }, "borderColor": { "solid": { "color": "#3e3e3e" } }, "transparency": 0, "titleSize": 18, "headerSize": 8, "fontFamily": "Segoe UI", "border": true } ], "filterCard": [ { "$id": "Applied", "backgroundColor": { "solid": { "color": "#0c62fb" } }, "foregroundColor": { "solid": { "color": "#ffffff" } }, "borderColor": { "solid": { "color": "#c2d8fe" } }, "inputBoxColor": { "solid": { "color": "#ffffff" } }, "transparency": 0, "textSize": 11, "fontFamily": "Segoe UI", "border": true }, { "$id": "Available", "backgroundColor": { "solid": { "color": "#c2d8fe" } }, "foregroundColor": { "solid": { "color": "#3e3e3e" } }, "borderColor": { "solid": { "color": "#c2d8fe" } }, "inputBoxColor": { "solid": { "color": "#c2d8fe" } }, "transparency": 0, "textSize": 10, "fontFamily": "Segoe UI", "border": true } ] } }, "*": { "*": { "*": [ { "responsive": true, "wordWrap": true, "fontSize": 10, "backColorPrimary": { "solid": { "color": "#ffffff" } }, "backColorSecondary": { "solid": { "color": "#ededed" } } } ], "title": [ { "show": true, "fontColor": { "solid": { "color": "#535353" } }, "background": { "solid": { "color": "#ffffff" } }, "alignment": "center", "fontSize": 12, "fontFamily": "Segoe UI" } ], "background": [ { "show": false, "color": { "solid": { "color": "#ffffff" } }, "transparency": 0 } ], "lockAspect": [{ "show": true }], "border": [ { "show": false, "color": { "solid": { "color": "#ffffff" } } } ], "visualTooltip": [{ "type": "Default" }], "stylePreset": [{ "name": "None" }], "datalabels": { "color": { "solid": { "color": "#0c62fb" } } }, "wordWrap": [{ "show": true }], "labels": [ { "show": true, "labelOrientation": "horizontal", "color": { "solid": {} }, "fillcolor": { "solid": {} }, "enableBackground": true } ], "categoryLabels": [ { "show": true, "labelColor": { "solid": {} } } ], "categoryAxis": [ { "showAxisTitle": true, "labelColor": { "solid": {} } } ], "valueAxis": [ { "showAxisTitle": true, "labelColor": { "solid": {} } } ] } }, "slicer": { "*": { "background": [ { "show": false, "color": { "solid": { "color": "#ffffff" } }, "transparency": 0 } ], "general": [ { "outlineColor": { "solid": {} }, "outlineWeight": 1, "orientation": "vertical", "responsive": true } ], "data": [ { "mode": "Basic", "relativeRange": "", "relativePeriod": "" } ], "selection": [ { "selectAllCheckboxEnabled": false, "singleSelect": true } ], "header": [ { "show": true, "fontColor": { "solid": { "color": "#c2d8fe" } }, "background": { "solid": {} }, "outline": "None", "textSize": 10, "fontFamily": "Segoe UI" } ], "items": [ { "fontColor": { "solid": { "color": "#0c62fb" } }, "background": { "solid": {} }, "outline": "None" } ] } }, "basicShape": { "*": { "general": [ { "outlineColor": { "solid": { "color": "#ffffff" } }, "outlineWeight": 0, "orientation": "vertical", "responsive": true, "background": { "solid": { "color": "#ffffff" } } } ], "border": [ { "show": false, "color": { "solid": { "color": "#ffffff" } } } ], "line": [ { "lineColor": { "solid": { "color": "#0c62fb" } }, "transparency": 0, "weight": 3, "roundEdge": 0 } ], "fill": [ { "show": true, "fillColor": { "solid": { "color": "#0c62fb" } }, "transparency": 0 } ], "rotation": [{ "angle": 0 }], "visualHeader": [{ "show": false }] } }, "waterfallChart": { "*": { "sentimentColors": [ { "increaseFill": { "solid": { "color": "#0c62fb" } }, "decreaseFill": { "solid": { "color": "#efbfbf" } }, "totalFill": { "solid": { "color": "#535353" } }, "otherFill": { "solid": { "color": "#c2d8fe" } } } ], "breakdown": [ { "maxBreakdowns": 8 } ] } }, "textbox": { "*": { "visualHeader": [{ "show": false }], "background": [ { "show": false, "color": { "solid": {} }, "transparency": 0 } ] } } } }

Report page with the original theme.

New theme

Sometimes you only want to change a few things. Then your JSON file does not have to be that extensive. To change the colors and the font, the following file is enough:

• Make a copy of the file and change the font (fontFamily) to the font you want.

{"name": "Multimedi", "dataColors": [ "#9DD562", "#5E8239", "#D9F8B9", "#F2C80F", "#7B6604", "#F6E082", "#FE9666", "#A66999" ], "visualStyles": {"*": {"*": {"*": [{"fontsize":16,"fontFamily": "Montserrat", "color":{"solid":{}}}]}}}}

• Choose your new JSON file as the theme.

Go to the ‘View‘ tab and click the arrow next to the themes.

Choose ‘Browse for themes‘ and navigate to the place where you saved your file.

• What is the difference between a report and a dashboard?

• What is the difference between datasets and workbooks in the PowerBi Service?

Workbooks are a special type of dataset. But you may wonder why Power BI classifies an Excel workbook as a dataset in some cases and as a workbook at other times.

The difference is made when you work from the service.

‹›

1 / 2

If you use the Get data option with Excel files, you have the choice to Import or upload the file. When you choose the Connect option, your workbook is shown in Power BI, exactly like in Excel Online. But unlike in Excel Online, you get a number of handy features that let you pin elements from your worksheets straight to your dashboards.

You cannot edit your workbook in Power BI. But if you want to make changes, you can click Edit and then edit your workbook in Excel Online or open it in Excel on your computer. Any changes are saved in the workbook on OneDrive.