Explore Power BI Desktop

I updated my current Power BI Desktop version via the Windows Apps Store recently, and now it is a good time to share the new user interface (UI) of the Power BI after the installation. In the year 2019, during my Specialized Diploma study, the Power BI Desktop skinning was in dark mode. I am not sure when the Microsoft team has changed the Power BI Desktop’s skinning to white mode, as well as having the Filter Pane on the right side.

Another new feature that I spotted is the Power BI has the theme options for the dashboard and reports. This theme is not referring to the Power BI Desktop’s skin. You have to enable this feature from the Power BI Settings, and it allows you to change the theme to suit your dashboard and reports presentation.

To do so, navigate to the File menu, select Options and Settings, then Options. Next, in the Preview feature section, select Customize current theme.

Click OK button to proceed. It may prompt you to restart the machine so that it takes effect and enables the theme feature. There is a list of built-in theme available in the Power BI Desktop, and you can refer to this link for more detail. Furthermore, you can optionally export a theme’s JSON file. You can make amendments by manually modifying the settings in that file. You can rename that fine-tuned JSON file and later import it. It gives more control to the users to customize the theme according to their dashboard and reports.

Getting familiar with the interface

From the Microsoft website, it shares the detail of each pane labelled below. I extracted the picture and its explanation.

  1. Ribbon – Displays common tasks that are associated with reports and visualizations.
  2. Report view, or canvas – It is a place where visualizations are created and arranged. You can switch between ReportData, and Model views by selecting the icons in the left column.
  3. Pages tab – This area is where you would select or add a report page.
  4. Visualizations pane – It is the pane where you can change visualizations, customize colours or axes, apply filters, drag fields, and more.
  5. Fields pane – It is the pane where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.

You can collapse the Visualizations and Fields panes to provide more space in the Report view by selecting the small arrow.

The screenshot above shows an example of the collapsible pane for Filter pane. It works for Visualizations and Fields panes too.

Connect to data sources

Power BI Desktop connects to many types of data sources, you can choose from local databases, excel sheets or data on the cloud. There are about 70 different types of data sources available. Go to the Get Data from the ribbon on the Home tab to begin accessing the data. Then, select a source to establish a connection. For some data source connection, you may require to input the user credential to authenticate and accessing the data. Here is the list of data connectors available in the Power BI’s Get Data function.

It brings you to the Navigator window that displays the entities (tables) of your data source. It gives you a preview of the selected data. In the same window, you can choose to Load or Transform Data. If you are not making any changes, formatting and data transformation, then you can click on the Load button, else Transform Data allows you to perform data cleaning and conversion before importing the data into the Power BI Desktop. You are allowed to edit the data after importing too.

Transform data to include in a report

Power BI Desktop includes the Power Query Editor tool that helps you shape and transform data so that it is ready for your visualizations. To launch the Power Query Editor tool, there are two ways to bring up this window:

  1. use Transform Data button on the Home ribbon. [For April/2020 version]
  2. use Edit Queries button on the Home ribbon. [For older versions]

If you click on the Enter Data button on the Home ribbon (as shown above), a Create Table window prompts up. From this window, click the Edit button, it brings up the Power Query Editor tool. Remember, earlier I mentioned about the Load and Transform Data buttons when we load data from the Get Data button? The Transform Data button brings up the Power Query Editor too, similar function as to how the Create Table window’s Edit button works. I am not going to cover any data transformation in this blog. It is a big topic to discuss, so I think it is good to share it with some good examples and dataset in the next article.

Connect from multiple sources

Most of the time, we deal with more than one data source when we build a report. You can use the Power Query Editor tool to combine data from multiple sources into a single report. How does it able to combine into a single table? In Power BI Desktop, it has a feature called Append Queries to add the data from a new table to an existing query.

Create a visual

If I remember correctly, in Tableau, when fields are selected, the Tableau suggests the suitable visualizations to the users to use in the dashboard or reports. I am not sure whether Power BI has a similar feature. In the Report View, drag a field onto the Report View canvas, the Power BI Desktop automatically creates a table visual as default visual. This visual as a report listing because it lists the selected fields in a tabular form. You can choose to have different visuals, such as a bar chart or line graph if you wish to do so.

To create a visual, select a field from the Fields pane, you can drag the field into the data field (Values) in the Visualization pane, or you can click on the checkbox. A table visual displays on the screen, and you can choose another type of visual from the Visualization pane. There is no precedence to create a visual, and you can select a visual before selecting the fields. Each visual has a different visualization pane, for example, if you choose a dual chart, the following screenshot shows shared axis, column and line values. When you choose a pie chart, it displays legend and values.

Publish a report

After all the hard work on the dashboard or reports, you want to publish it and share it with other people. You can do so in Power BI Desktop by clicking on the Publish button in the Home menu. You will be prompted to sign in to Power BI, follow the steps and you will see the published reports after that.

At this point of writing, I do not have any published report to show. Therefore, I cannot put up the steps here and show how to pin a visual to the dashboard. This feature allows you to choose whether to pin the visual to an existing dashboard or to create a new dashboard.

Conclusion

This article is a high-level walkthrough of the Power BI Desktop, that explains how to use it to create visuals and publish the dashboard and reports. I do not cover the explanation of the visualization and publication in this article, I will include them in the next article in the future.

I hope this article gives a good impression of the Power BI Desktop’s features and allows you to have some sensing of this tool. Furthermore, the Power BI Desktop’s buttons are self-explanatory, so you should not have issues or troubles to use and navigate around. Besides that, people who have been using Microsoft Excel and Tableau for data analysis may find the Power BI Desktop has some similar functions because the Power BI Desktop is another data visualization tool too.

Reference: microsoft.com

What is the difference between dashboard and report in Power BI?

I have written about Power BI Desktop and the high-level walkthrough in my previous articles. Both articles I mentioned two keywords, dashboard and reports quite often. In this article, I would like to share about the difference between the dashboard and report in the Power BI Desktop. In general, a dashboard consists of a visual that displays the most crucial information needed to achieve objectives, consolidated and arranged in a single screen. We can use a dashboard to monitor at a glance. A report, on the other hand, is any information that can be at any visuals, such as a table, chart, and etc.

Power BI dashboard is one placeholder

Dashboard displays the most important decision making facts to run a business. Power BI dashboard is a high level view of some key reports, such as Key Performance Index (KPI). It uses to provide day to day information consolidated from the detailed reports. Below is one of the example of a Power BI dashboard.

A Power BI dashboard is a single page, often called a canvas, that tells a story through visualizations. The visualizations you see on the dashboard are called tiles. The visualizations on a dashboard originate from reports and each report is based on a dataset. Readers can view related reports for the details.

Dashboards are a feature of the Power BI service only. They’re not available in Power BI Desktop.

Features of a Power BI Dashboard

  • Automatic refresh.
  • Sharing to users.
  • Power Q&A.
  • Real-time monitoring.
  • Send alerts.

Next, let us move on to the Power BI Report.

Power BI Report

Power BI reports are more detailed data presented in many formats like charts, graphs, tabular, etc. You can have more than 1 page for reports, as shown in the picture above. The data in the report can be used to perform analysis. For example, by using a standard filter function called slicer, the users slice the data according to the filtering criteria. It allows the user to drill down through selecting particular data, and then the report interactively changes. Hovering and highlighting data in Power BI reports give the same interactive changes experience to the user.

Features of Power BI Reports

  • Slicers.
  • Multi-pages.
  • Interactivity.
  • Drill down to explore data.
  • Publish to the Website.

Comparison between the dashboard and report

The Educba website provides a good visual comparison between the Power BI Dashboard and Report. You can refer to the link here.

Key Differences Between Power BI Dashboard vs Report

Below are the important differences:

  • Different data sources are used by power BI dashboard in each dashboard consists of maps, graphs, etc. Only one dataset can be used as a source for Power BI reports.
  • Power BI dashboard capabilities of pinning existing visualization are only from the current dashboard to other dashboards, called as dashboard navigation. The Power BI reports can pin or navigate to any reports or dashboards.
  • Email subscription for power BI dashboard is not available. Reports can be published on the web and subscribed through email.
  • Power BI dashboards have alerting feature where attention needed condition arise dashboards raise an alert, but in reports, we cannot have this feature.
  • Power BI dashboards cannot slice and dice, but reports have many ways to filter and slice.
  • Natural language queries available only available from the dashboard, but for reports, it’s not possible.
  • Power BI visualization cannot be changed, but reports visualization can be changed and saved, but it all depends on user role and permissions involved to access dashboards and reports.
  • Dashboards have the same features of reports, but dashboards can be exported to limited formats, so dashboards are used to visualize the important data rather than analyze data. Data can be exported for the reports only, any formats like excels tabular formats.
  • Power BI dashboards can be set as featured or as favourites but cannot create a featured power BI reports.
  • Power BI reports can be drilled down to different levels of data sets on which reports are built.

Conclusion

At a glance, a dashboard and a report serve different purposes, and they have its own unique features. For good data visualization, you may want to include both dashboard and reports because they complement each other.

References:
https://docs.microsoft.com/en-us/power-bi/service-dashboards
https://www.educba.com/power-bi-dashboard-vs-report/

Salmon Recipe – Honey Garlic

During this circuit breaker period in Singapore, many of us are trying our hand to do some home-cooked food for the family. And, I am trying to improve my cooking skill too. There is a lot of recipes being shared by people since I joined the home-cooked Facebook group. Previously, I shared how I prepared the Thai Pad Woonsen.

Western Style

This blog shares how I prepared the honey garlic salmon. It is an easy recipe, and the ingredients are easy to get. Most importantly, it takes 15 minutes to make a salmon ready for dinner. Please refer below reference link for the recipe.

Ingredients

You can buy the salmon fillet from the supermarkets or wet markets, and cut them into two or three portions depending on the size of the salmon. Next, I prepared the sauce that consisted of the following:

  • 2 tablespoons of honey.
  • 1 1/2 tablespoons of apple cider vinegar.
  • 1 tablespoon of olive oil.
  • a pinch of salt and some pepper.
  • 1 tablespoon of warm water.
  • 3 garlic cloves, finely chopped.

I mixed all the above in a saucer, and chopped some spring onion, to replace the parsley. For the salmon, I marinated it for about 30 minutes with salt and pepper. The salt and pepper are the basic ingredients to marinate salmon. If you like a flavourful salmon, you can add other spices before start pan-frying it.

Start Cooking

Heat up the pan on high-heat and add some oil on the pan to pan-fry the salmon. Always wonder, which side I should put first. The answer is skin side down first. Let it pan-fry for about a minute, you can slowly see it turning its colour. After a minute, I turned the salmon over and cooked for another minute. And, again I turned the salmon over after both sides cooked.

Next, I added the garlic together with the honey mixture, saute until garlic until the brownish colour, and the honey mixture turned thicker. It took me for about 10 minutes to complete the cooking under medium-high heat. After that, I off the fire and added some spring onions on top of the salmon, Served it on the plate while it is still hot.

My verdict

I liked the thickened honey garlic sauce and the burned garlic. Although I did not follow precisely the recipe to add the lemon into the pan while sauteing, the taste was just right to my tastebud, and the salmon was evenly cooked. It is a simple and delicious recipe that everyone can follow. Please try it yourself with the recipe below.

Reference: https://rasamalaysia.com/honey-garlic-salmon/

Pad Woonsen (Thai Fried Glass Noodle)

During this circuit breaker period in Singapore, many of us are trying our hand to do some home-cooked food for the family. And, I am trying to improve my cooking skill too. There is a lot of recipes being shared by people since I joined the home-cooked Facebook group.

Thai cuisine

In this article, I am sharing my experience cooking the Thai food called Pad Woonsen, a Thai fried glass noodle. I followed the recipe from Marion’s Kitchen, refer below for the recipe’s link. She is one of my favourite chefs.

Ingredients

I used the chicken meat, cut into small cubes, and marinated it with salt and pepper. I would recommend marinating the meat for at least 30 minutes before cooking. Then, I sliced an onion, shredded a carrot, and chopped some garlic cloves. Also, I prepared an egg and some spring onions that use for garnishing. On the other hand, I soaked a bunch of glass noodle into warm-hot water, not boiling water for about 8 to 10 minutes until it tendered. Drain the water and set it aside for cooking later. Before I started cooking, I made the stir-fry sauce which consisted of some oyster sauce, 2 tablespoons (tbsp) of fish sauce (* essential ingredient) and a bit of dark soy sauce (the thicker type of soy sauce). You may want to adjust the among of oyster sauce based on your tastebud. Now, we are ready for cooking.

Start Cooking

It started with stir-frying the garlic and onions for about a minute until it was fragrant. Then, I added the marinated chicken meat and stir-fried together with onions and garlic for another few minutes, until the chicken meat well-cooked. I like cooking the garlic along with the chicken meat because I want the chicken meat absorbs the garlic’s aroma while cooking. Next ingredient was putting in the carrots to stir-fry before adding the egg into the middle of the pan. As per suggested, I spread the egg out and allowed them to set before breaking them up and tossing them thoroughly with the rest of the ingredients. Lastly, I added the glass noodle and the sauce, stirred until well mixed. You can cut the glass noodle into the small threads before putting it into the pan because it is easier to toss them together. You can further adjust the taste by adding some salt, pepper or sugar.

Before off the fire, I garnished the glass noodle with some chopped spring onions. For this spring onions, I cut them into about 3 centimetres long. I got a colourful pad woonsen served hot in my bowl.

My verdict

The overall taste of the glass noodle was good, I controlled the saltiness level of the stir-frying sauce that consisted of the fish sauce and oyster sauce. You can try to follow this recipe below.

References: https://www.marionskitchen.com/pad-woonsen/

Power BI – Learning new skill

Recently, I get access to the abundance of online learning resources for Microsoft Power BI. I learned the fundamental of using the Power BI in my Specialized Diploma course. Now, it is a good time to recap what I have learned.

So, what is Power BI? It is a Microsoft product. It is a business analytics service that delivers insights to enable fast, informed decisions. This software has both free version and paid versions, Pro and Premium (with different subscription fees and features). Small introduction of what is Power BI and its versions as below.

What is Power BI Desktop?

The Power BI Free/Desktop enables you to connect to 70+ data sources, analyse data, publish to the web, export to excel and much more. The free version gives you the basic features of Power BI.

What is Power BI Pro?

Power BI Pro is the full version of Power BI, which means it comes complete with the ability to use Power BI for both building dashboards, reports and unlimited viewing, sharing and consumption of your created reports (and reports shared by others) which is not possible with Power BI Desktop.

What is the difference?

  • Power BI Pro has the ability to share the data, reports, and dashboards with a large number of other users that also have a Power BI Pro license.
  • Power BI Pro able to create an app-based workspace.
  • Power BI Pro has a 10 GB per Pro user data storage limit.

Maybe, these differences are a little irrelevant if you just want to learn Power BI for leisure instead of using it for commercial usage. For personal learning, I did not need to use up to 10gb data. As long as my email account is valid, I can start using the Power BI.

What is the Power BI App?

All Power BI’s versions can be connected via mobile applications. Furthermore, the Power BI Mobile applications are available for multiple platforms including Android, iOS and Windows devices.

What is Power BI Report Server?

Power BI Report Server is an on-premises (at your own location) server that publish and share both Power BI reports via the website within your organisation’s firewall (infrastructure). Power BI On-Premise or Report Server is an option included with Power BI Premium and is ideal for your business if you want to establish reporting infrastructure on-premises and have it operate under your own policies and rules. The server allows you to seamlessly scale up and move to the cloud if you wish to do so.

The above is a visual that helps to understand all the above. These three elements—Desktop, the service, and Mobile apps. The Power BI Desktop accesses the data and creates the dashboard and reports. Then, publish to the Power BI Service, and share the Power BI reports to users, who can access it via Power BI Mobile too.

By now, you may start getting familiar with some of the terms used in the Power BI. These are some of them:

  • Dashboard or visualization or tile. A tile is a single visualization on a dashboard or report. Visualization is a visual representation of data, like a chart. A dashboard is a collection of visuals from a single page.
  • Reports. A report is a collection of visualizations that appear together on one or more pages.
  • Datasets. A dataset is a collection of data that Power BI uses to create its visualizations.

The example above shows the dashboard contains the bar charts, line graph and cards. These are different visualizations available in Power BI, and the red box refers as a tile.

Limitations: Power BI Free/Desktop

As most of us in the learning stage will use the Power BI Free version, there are some feature limitations with Power BI Desktop.

  • Can’t share created reports with non-Power BI Pro users
  • No App Workspaces
  • No API embedding
  • No email subscriptions
  • No peer-to-peer-sharing
  • No support to analyse in Excel within Power BI Desktop

However, there are useful features available for Power BI Free/Desktop users.

Advantages: Power BI Free/Desktop

  • You can connect and import data from over 70 cloud-based and on-premises sources
  • The same rich visualisations and filters from Power BI Pro
  • Auto-detect that finds and creates data relationships between tables and formats
  • Export your reports to CSV, Microsoft Excel, Microsoft PowerPoint and PDF
  • Python support
  • Save, upload and publish your reports to the Web and the full Power BI service
  • Storage limit of 10 GB per user

I will be sharing more about Power BI Desktop from time to time as part of my learning objectives and improving my technical writing. Hope to hear some feedback from my readers from time to time. Please help me to fill up the survey form so that I can improve in my next blog.

References:
https://dynamics.folio3.com/blog/difference-between-power-bi-pro-vs-free-vs-premium/
https://docs.microsoft.com/en-gb/learn/modules/get-started-with-power-bi/1-introduction

What is the difference between calculated columns and measures in DAX

Since my school time in Temasek Polytechnic, I have been confused between the calculated columns and measures that available in Power BI. There was no distinguish example to explain them. I try my luck to search around and get a better understanding of these two fields.

The calculated columns and measures are one of the few concepts to learn and master in DAX.

What is DAX?

It stands for Data Analysis Expressions (DAX). It uses to perform basic calculation and solve data analysis problems. According to the Microsoft website, DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.

This may be similar to the Microsoft Excel formulas. With that knowledge, it will be useful for DAX understanding.

What is the measure?

According to the Microsoft website, it explains that the measures are used in some of the most common data analyses. Simple summarizations such as sums, averages, minimum, maximum and counts can be set through the Fields list well. The calculated results of measures are always changing in response to your interaction with your reports, allowing for fast and dynamic ad-hoc data exploration. 

It is useful whenever you do not want to compute values for each row but, rather, you want to aggregate values from many rows in a table.

How?

In Power BI Desktop, a measure is created in Report view or Data view. The new measures appear in the Fields list just like other fields, but it will have a “calculator” icon showing its values. You can name the measures, and add them to a visualization just like other fields.

Measures calculate a result from a DAX formula. An example of how to create a measure can be found in this link. It uses the DAX formula to sum the sales of the year, and calculate sales projections for the coming year. It is expected 6% increase in business.

Projected Sales = SUM('Sales'[Last Years Sales])*1.06

What is a calculated column?

With calculated columns, you can add new data to a table already in your model. But instead of querying and loading values into your new column from a data source, you create a DAX formula that defines the column’s values.

How?

Unlike custom columns that are created as part of a query by using Add Custom Column in Query Editor, calculated columns that are created by using the New Column feature in Report view or Data view are based on data you’ve already loaded into the model.

For example, you might choose to concatenate values, do addition, or extract substrings from two different columns in two different but related tables. The calculated columns appear in the Fields list just like other fields, but it will have a different icon showing its values are the result of a formula. You can name the calculated columns, and add them to a visualization just like other fields.

An example on how to create a calculated field can be found in this link. It uses the DAX formula to create a concatenated string from two columns.

One important concept that you need to remember about calculated columns is that they are computed during the database processing and then stored in the model. In more technical explanation, in data models for DAX, all calculated columns occupy space in memory and are computed during table processing. If you familiar with SQL computed columns, they are computed at query time and do not use memory.

Therefore, when computing a complex calculated column, the time required to compute is always process time and not query time, resulting in a better user experience. However, you have to remember it uses our precious RAM.

The differences

The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. 

A measure operates on aggregations of data defined by the current context, that depends on the filter applied in the report, such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

References:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics

I would like to get feedback from my reader

As per title, I would like to gather some feedback from the reader regarding my blog. I have been writing on food reviews and some technical knowledge sharing. Appreciate your kind help to give some feedback to improve my writing skill and the content of the blog. Below is the link.

https://drive.google.com/open?id=1khYP9ayDGGlnjEHnospW7hWpdGu90ZKYEOzdrntRWN4