PowerBI Zero to Hero

Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.

Business users spend up to 80 percent of their time on data preparation, which delays the work of analysis and decision-making. Several challenges contribute to this situation, and Power Query helps address many of them.

Through this Chapter we will see a different range of operations that can be performed in the Power Query Editor also “Load & Transform Data” or “Edit Queries”

With Power Query in Power BI, you can connect to many different data sources, transform the data into the shape you want, and quickly be ready to create reports and insights. When using Power BI Desktop, Power Query functionality is provided in Power Query Editor.

Through this Video, We explore what is the Power Query editor, we Connect to a database then we perform a simple operation of changing the data types of the different columns.

In Power Query, you can include or exclude rows based on a column value. A filtered column contains a small filter icon ( ) in the column header. If you want to remove one or more column filters for a fresh start, for each column select the down arrow next to the column, and then select Clear filter.

In the video we explore more details on how to filter

You can remove columns from your query that you don't need. You may pick one or more columns, then delete the selected ones or the unselected ones, i.e. the remaining columns.

In the video, we’ll show an example of removing columns unwanted and reordering the rest to fit your needs, this will make your model smaller in size, and faster in speed!

In this video we learn how to create a conditional column in Power BI. A conditional column is simply another column that is created as a result of a condition being imposed on an existing column. A basic use case would be to divide users into Child and Adult categories based on their age. Alternatively, you can use a value to assign a category to a row.

Let's take a look at this together.

Remember when I told you Power BI allows you to connect to multiple data sources at the same time in the same place? It’s time to see how it works!

One useful function of Power BI is the ability to import data from several files in a folder. To be able to do this, the files must have the same schema (same number of columns, same column names, same datatype per columns, etc.).

A lot of the time, your data for the same subject can be in multiple separate tables, for analysis, you might want to combine that data into a single big table to have one overview of it, This might be in a case where data for multiple months or years is in separate excel sheets, or data for different countries is in different databases, or as in our example, US data is in a database table, and other countries data is in separate CSV files.

We will learn through this video how to combine data from multiple tables and how to clean it afterwards using some simple M Language Code.

Another common occurrence is that your Data will come in a format not very suitable for analysis, this is often when it’s of the wrong data type, or it’s in a matrix-like structure, or what we call pivot tables, where you have “titles” in both the columns and the lines

You want to adjust that structure a little bit so that it looks much more like a table that has only one row of headers and the rest is just data points, that’s what we’ll learn to do in this video.

Sometimes there’s no way to find out about errors in your data until you hit that “Close and Apply” button, this video briefly shows you one of those examples.

Here’s the data scenario we will be using in the next few exercises:

VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdel’s sales come from its owned manufactured products, as well as other manufacturers’ products.

VanArsdel's US office stores the sales data on an Access database. VanArsdel International sales transactions are available as comma separated (CSV) files. They could be generated daily, either manually by someone, or automatically by an automated process. They are available in a dedicated folder. These CSV files have the same column structure as the sales table for the US sales that comes from the SQL Database.

You want to perform analysis on VanArsdel's worldwide sales data for the year 2000 to 2015. You need to bring all these data into Power BI Desktop before you can perform any analysis. Finally, you want to compare VanArsdel's country sales with the country population. You need to import the country population data from a less structured Excel report to Power BI.

Go back to the ressources video so you can download the ressources before you begin this exercice.

Data modeling is the practice of shaping the different tables and the relationship between them in order to make your analysis and calculations easy to perform, fast and efficient. This practice also makes your dashboards easy to update and maintain, in this chapter we will see how to model your data in a way that will make your job so much heasier.

Power BI automatically tries to identify relationships in your data, but a lot of the time you have to add some relationships and manage or delete exiting ones, here we have a look at what are relationships and how to create new ones when there is Ambiguïty.

We have a Zip Code column that has the same values existing in multiple countries at the same time. Thus, we cannot create a relationship that’s based on Zip Code because it will cause confusion and create different results, To deal with it, we create a new calculated column to solve this ambiguity.

3Dmenal Modeling & Star Schemas

Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either dimension or fact.

Dimension tables describe business entities—the things you model. Entities can include products, people, places, and concepts including time itself. The most consistent table you'll find in a star schema is a date dimension table. A dimension table contains a key column (or columns) that acts as a unique identifier, and descriptive columns.

Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc. A fact table contains dimension key columns that relate to dimension tables, and numeric measure columns. The dimension key columns determine the dimensionality of a fact table, while the dimension key values determine the granularity of a fact table. For example, consider a fact table designed to store sale targets that has two dimension key columns Date and ProductKey. It's easy to understand that the table has two dimensions. The granularity, however, can't be determined without considering the dimension key values. In this example, consider that the values stored in the Date column are the first day of each month. In this case, the granularity is at month-product level.

Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a very large number of rows and continue to grow over time.

Let’s apply this to our model !

Often when we pull our data model for the source, it will contain a lot of columns that are used to define relationships and calculations, but not necessarily useful for analysis, looking for the columns you need through a list of ALL the columns of IDs and bits of information you don’t need might be not a very pleasant experience and could waste you some time when developping your reports. Even worse, It could confuse other users trying to build something on top of your model,

In this section we clear the confusion !

You have successfully brought the US sales data from the Access database and the International sales data from a collection of CSV files to Power BI Desktop. Before you can start analyzing your data, you need to manage the table relationships within your data model and create new ones if necessary. To do so, you might need to create calculated columns or calculated tables for the relationships to be based on.

DAX stands for Data Analysis Expressions, it is language developed by Microsoft to interact with data in a variety of their platforms like Power BI, PowerPivot and SSAS tabular models. It is designed to be simple and easy to learn while exposing the power and flexibility of tabular models. In a way, you could compare it with Excel formulas on steroids. Using DAX will truly unleash the capabilities of Power BI.

Let’s see one really cool feature of measures; they use the DAX language to create calculations that you can slice & dice across multiple areas of the business without having to write any additional code, you just write the code once, and you drag and drop what you want on top of it.

In this video we explore CALCULATE a DAX function for Power BI , the most powerful one, which allows us to use a different context for our calculation. We also have a look at SamePeriodLastYear, a function in DAX For power bi which is typically a function used to compare this year’s value to previous year’s value.

Waterfall charts show a running total as Power BI adds and subtracts values. They're useful for understanding how an initial value (like revenue) is affected by a series of positive and negative changes.

The columns are color coded so you can quickly notice increases and decreases. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns. Because of this style, waterfall charts are also called bridge charts.

We will use this combined with some more DAX formula to see the divergence of our Sales across time and how they evolved.

YTD information is useful for analyzing business trends over time or comparing performance data to competitors or peers in the same industry. The acronym often modifies concepts such as investment returns, earnings and net pay. Let's see how fast we can perform YTD calculations in Power BI !

Once you have all the relationships created, you can create visualizations and start to analyze the data. However, you need to create additional measures to perform more advanced analysis with your data, which includes:

Comparing last year sales and last year YTD sales.

Comparing sales of VanArsdel's manufactured goods to other manufacturers.

With so much information being collected through data analysis in the business world today, we must have a way to paint a picture of that data so we can interpret it. Data visualization gives us a clear idea of what the information means by giving it visual context through maps or graphs. This makes the data more natural for the human mind to comprehend and therefore makes it easier to identify trends, patterns, and outliers within large data sets.

All the effort we’ve done so far will lead to it’s fruits in this section, Stay tuned to create beauty within your Dashboards !

Creating a Power BI Pie chart can be done in just a few clicks of a button. But then there are some extra steps we can take to get more value from it.

The purpose of a Pie chart is to illustrate the contribution of different values to a total.

For example, to see the total sales split by product category. You can then see the percentage contribution of each product category to the total revenue.

The Pie chart is not the only chart type that can produce this visual. We could opt for a Donut chart, or maybe a Treemap instead.

It is important to be able to create different chart types, as you may be asked by someone to display data in a specific way.

Check out this quick video on how to create a Power BI Pie chart.

When a visual has a hierarchy, you can drill down to reveal additional details. For example, you might have a visual that looks at Olympic medal count by a hierarchy made up of sport, discipline, and event. By default, the visual would show medal count by sport: gymnastics, skiing, aquatics, and so on. But, because it has a hierarchy, selecting one of the visual elements (like a bar, line, or bubble), would display an increasingly more-detailed picture. Selecting the aquatics element would show you data for swimming, diving, and water polo. Selecting the diving element would show you details for springboard, platform, and synchronized diving events.

Dates are a unique type of hierarchy. Report designers often add date hierarchies to visuals. A common date hierarchy is one that contains year, quarter, month, and day.

You can also create custom hierachies depending on your business needs, in this course we show you how.

In this video, we will learn to filter the data is the visual for the top n records. For example top 3 countries by sales revenue or top 5 products by revenue etc.

This will allow you to reduce the amount of clutter you see on your screen and allow you to focus on only the data that’s most important.

In Power BI, a combo chart is a single visualization that combines a line chart and a column chart. Combining the 2 charts into one lets you make a quicker comparison of the data.

Combo charts can have one or two Y axes.

When to use a Combo chart

Combo charts are a great choice:

• when you have a line chart and a column chart with the same X axis.

• to compare multiple measures with different value ranges.

• to illustrate the correlation between two measures in one visualization.

• to check whether one measure meet the target which is defined by another measure

• to conserve canvas space.

In visualization and BI, once you've got your report and visuals ready, you might still want to do some further analysis. For example, you might want to highlight a line indicating the minimum, mean or maximum values in the chart. In Power BI, the analytics pane captures all the analytical options available for any selected chart at your disposal. The analytics pane can serve even to do Forecasting of future values or even perform advanced analytics like Regression!

In this video, you will learn how to explore and implement the analytics pane in Power BI desktop.

In a time where the average enterprise generates large amounts of data on a daily basis, unless the data paves a path to gleaning valuable insights, on its own, data does not hold much value. This is where Artificial Intelligence helps aid data analysis, exploration, find patterns in the collected information, predict future outcomes and make data more comprehensible for the user.

Microsoft’s Power BI is one such application that has broken new grounds in applying AI to Business Intelligence by means of NLP (Natural Language Processing), machine learning, and advanced analytics.

In this video, we will look at the key AI features in Power BI that you must start using right away.

Suppose you want your report readers to be able to look at overall sales metrics, but also highlight performance for individual manufacturers, categories and different time frames. You could create separate reports or comparative charts. You could add filters in the Filters pane. Or you could use slicers. Slicers are another way of filtering. They narrow the portion of the dataset that is shown in the other report visualizations.

Let’s explore them through this video.

Power BI integrates with Bing Maps to provide default map coordinates (a process called geo-coding) so you can create maps. It also has many different options for mapping in your data.

Also As Power BI is widely used for data visualization and analytics, one bottleneck with analytics is the lack of highlighting essential data and data points in charts and reports. In this tip we will look at how to use conditional formatting in a Power BI chart to address this need.

Power BI recently introduced a dynamic and color saturated support for "Conditional Formatting" with the majority of charts. This feature allows you to configure the color bifurcation not only for a table or matrix, but also on a variety of charts as well based on a certain value.

More on that in the video.

In this video, We see the Table visual and how we can customise it. A table is a grid that contains related data in a logical series of rows and columns. It may also contain headers and a row for totals. Tables work well with quantitative comparisons where you're looking at many values for a single category

Tables are a great choice:

• To see and compare detailed data and exact values (instead of visual representations).

• To display data in a tabular format.

• To display numerical data by categories.

In Power BI reports, you can change the color of data series, data points, and even the background of visualizations. You can change how the x-axis and y-axis are presented. You can even format the font properties of visualizations, shapes, and titles. Power BI provides you with full control over how your reports appear.

This is a crucial step in your data visualisation because it will allow you to push your reports so much further.

When building a Power BI report, odds are that all of the information you want to display will not fit on one page. Because of this, it is important to provide an intuitive way for your users to navigate through the different pages of your report. In this blog post I will talk through a few different solutions for page navigation in your Power BI reports.

After you have created the data model, the calculations necessary, it’s time to visualise it to present it to the people that will make decions based on it In your organisation.

Presenting data visually will allow you to visualise it yourself too and gain so much insight into the data!

In these exercices you will see the minimalist version of the reports, feel free to customise your report page, colors and background to showcase your exquisite taste!

Now that you’ve done all of the hard work, it’s time to show your report to the word ! … or at least to the people who should have access to that data and need it.

Power BI offers your a plethora of options for sharing, distribution and collaboration on the solutions you build that allow you to scale them very efficiently. In this chapter we’ll explore how to publish and share a report with your collaborators.

Power BI is an incredible business analytics tool. However, your reports are useless if you can't share them with others. In this video, we will see how you can Publish a report to the Power BI Service

The Microsoft Power BI service (app.powerbi.com), sometimes referred to as Power BI online, is the SaaS (Software as a Service) part of Power BI. In the Power BI service, dashboards help you keep a finger on the pulse of your business. Dashboards display tiles, which you can select to open reports for exploring further. Dashboards and reports connect to datasets that bring all of the relevant data together in one place.

In this video we go over the key features of the Power BI Service.

Whether it’s using interactive dashboards to consolidate key metrics or rich reports to connect datasets from workloads, Power BI is a key tool to engage with business data, pull it from a broad range of disparate sources, and enable smarter data-driven decisions.

I and the Business Analytics community at large believe that Power BI is going to be huge, and we go over some of the reasons as to why.