Power BI Cannot Use SQL CTE

I just want to do a quick sharing of what I have found out today in Power BI Desktop. I was trying to use the SQL CTE in the Power BI Desktop to get a result set for my project’s testers to check the data transformation and ETL processes in the UAT database. A little bit background, we are using Power BI Desktop to access our data in the UAT and Production environment. Users do not have direct access to the databases in the server or access the data via SQL Server Management Tool (SSMS).

What is cte?

CTE stands for command table expression in SQL Server. According to the Microsoft website, CTE is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression. For more reading about CTE, please refer to the link in the reference list below.

It gaves error!

The CTE script that I wrote was running perfectly good in SSMS installed in my local machine. However, when I used the Get Data function in the Power BI Desktop and placed the same script into the SQL command text field, the Power BI Desktop threw an error on the screen that suggested it has a syntax error. I knew it was not having any syntax errors, even some of the online solutions mentioned that we have to put a semi-colon in the script.

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
-- Define the CTE query.  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear;

power bi does not support cte

I am quite agree with one of the Power BI users’ replies, the Power BI Desktop is supposed to be a self-servicing tool for users. In other words, it is not meant writing SQL scripts such as views or stored procedures. To resolve the error, I have to change the SQL statement to use normal SELECT statement. This is a good time to test the SQL skill.

I do not have the answer or SQL script for the above SQL statement, if you are looking for one because I just picked the sample script from the Microsoft website. If you have the solution, you can share it with me in the comment box below.

I hope this is simple enough for quick understanding and learning about SQL. You can check out other SQL topics in my blog from this link. If you have any feedback, please leave me a message in the comment box below.


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.


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.


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.


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

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.


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.


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.


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.


January 2020

I hope it is not late to write out my plans for the year 2020. My volunteer work with the TechLadies will come to an end, this March. The TechLadies is recruiting the new core team for the year 2020. The upcoming boot-camp graduation will introduce the new team to the community. Then, the year 2019 core team will pass the baton to the new team.

Will I still continue volunteering with TechLadies?

I have this question in my mind lately, and I am not sure how the TechLadies plans for it. I am quite sure there it would be a great idea to let a new team leads the community. New team, new ideas and directions.

I may consider taking a side role to continue on the study group sessions. But, I also hope that someone is going to plan and run the study group sessions together. If not, then I will be slowly running the events as and when I am available. I am not sure whether a mobile study group will work in Singapore.

Besides TechLadies, what else?

Good question. I have a plan to conduct, learn and teach program after being inspired by my classmate. This program teaches the community (not necessarily must be within TechLadies) of what I learned recently.

I will randomly pick up a topic to learn and share to the community via my blog or private meet-ups. I hope to get more interaction between community members, instead of just giving inputs without receiving feedback from the community.

I hope I will write and share more technical stuff through my blog here as well as my posts in the Medium website.

New focuses

I am looking out for other communities in Singapore that work closely on master data management (MDM), focuses on SQL and NoSQL databases, work on data engineering and use Power BI for data visualization.

I am not going away from my core interest, the databases. Also, I want to go in-depth into master data management and will consider taking some of the courses or certifications in this area. Next, I need to upskill and gain essential experience in the data engineering field while continue exploring the data visualization with Power BI. I am still looking out for Data Engineering meetup or users group in Singapore. Do you know any?

Not to forget, I am doing my data analytics in my final module in Temasek Poly. It is going to be an end-to-end data specialization when I graduate with my Specialized Diploma in Business Analytics this April.

Complete my Python course!

Last but not least, I want to complete my Python course before I graduate too, so that everything is fresh in my mind. Right now, I have completed 10/26 modules. I still need to complete some Pandas, statistics and machine learning topics before the end of February. Maybe, I will take a bit time off from other activities to focus on study and work.

Data Management Using Power BI

Data Management is the process of profiling, cleaning and transforming data sources into useful information. Data Management covers the areas of data profiling, data cleaning, data exploration, data integration and data transformation.

Currently, I am working on the part time course’s project which uses Power BI to do data profiling and data exploration of different datasets. It made me understanding the important of understanding the working datasets and know what story we want to tell as a conclusion before deciding what to be cleaned in the data cleaning process.

The project made me to understand, even there are some incorrect values or data needed to be cleaned and usually it is encouraged to be cleaned, yet it is not required to do so. I believed as long as we are able to justify the reasons of the data to be cleaned and not to be cleaned in our work and how does it affect to the overall data exploration.

Each steps serve different purposes in the ETL process.

  • Data Profiling – to get an overview structure of the data and assess the data quality.
  • Data cleaning – to improve the data quality.
  • Data exploration – to use statistics charts to get a sense of the distribution or correlation.

Data Profiling

  • Get a visual profile of the data to assess the structure and quality of the data. Using Power BI, you will get a table profile of summary statistics such as min and max values, distinct count, error or missing value counts.
  • It allows us to identify missing values and inconsistencies in the data. Based on this information, we can further assess the quality and plan for the data cleaning process.

The above view shows the Power BI Editor once we loaded the data into Power BI tool through the Get Data option. The Power BI supports different types of data files.

One limitation on this summary statistics is Power BI works on the first 1,000 records only. In other words, Power BI will not be able to show us any data error and missing values from 1001th row onward.

Similar data profiling can be found in other software tools such as SAS which allows its users to check the data quality using their tools.

Data Cleaning

Data cleaning or data cleansing is a process of detecting and correcting (or removing) incorrect values or records from the datasets and replacing, modifying and deleting the dirty data. Several things we can do during the data cleaning such as,

  • Remove duplication
  • Change text to lower/upper/proper case
  • Spell check
  • Remove extra spaces
  • Treat blank cells
  • Standardization

Are data cleaning technique essential?

The answer is yes. We spend 80% of our time in data cleaning and it is not only essentially important for data analytics and data science, it is most time consuming part to ensure the data always matches the correct fields, interact effectively and making it easy for data visualization.

Data Exploration

It is the part where the story-telling begins after all the datasets are cleaned and integrated. How well is the data transformed from its raw values and integrated together with all the datasets affect the overall quality of the data. It is important that we have sets of quality datasets before begin the data exploration.

Many times, in my experience of data exploration I found there are more data to be cleaned in order to get the right insights or stories that I want to tell in my dashboard. During the data profiling, these should be identified and any wrong outcomes from the data exploration maybe due to outliers which we overlooked earlier.

What is the difference between Distinct and Unique in Power BI?

I looked into the summary statistic which I shared previously and I was a bit confused to distinguish the different between “Distinct” and “Unique” when both of them give almost the similar meaning. In one of the datasets, the name column has “Distinct” value of 740. This means within the datasets there are 740 unique names.

Then, why the “Unique” value is not 740 also.

Here is the explanation I found in most layman way, “Distinct” means total number of different values regardless how many times it appears in the dataset. A name appears in the list multiple times is counted as 1 distinct count.

Whereas, the “Unique” value is total number of values that only appear once. This means there are 740 distinct names in the dataset and out of it there is 485 names has 1 record only.

Depend on the business use cases, for example in my project, it mentions that for client names appear more than once, keep the first record. Then, after some data cleaning, we will able to see the “Distinct” value and “Unique” value is the same in the statistics summary.

My experience using Power BI

As I mentioned in the previous entry, what data visualization tool to be used is depending on what kind of data you have, what is the purpose of your project and how you want to present it. My project does require me to demonstrate my ability to perform data exploration using Descriptive Statistics and apply techniques to clean data using Power BI.

I was given 3 sets of data. I was told that the data is messy because it used different external sources to compile it. I loaded the Power BI software tool on my machine, There is an option called “Get Data” found under the Home menu. It is able to load many types of data sources, from files to databases and online services and cloud.

Once the file is selected, in my case, I chose a .csv file. it will load into this dialog box to preview the source file. From here, it allows us to load or edit the data. We can edit the data anytime in the Power Query Editor. So, I chose to load the data first by clicking on the “Load” button.

I loaded all the 3 sets and under the “Fields” explorer on the right side of the tool, you can view the columns names for each of the datasets. Firstly, I noticed there is something wrong with the country code dataset. It did not read the first row as header, therefore my column name turned into “Column1”, “Column2”, “Column3”. Usually, Power BI able to identify the header in the excel file, just like the other 2 sets of data.

How to set first row as header?

I changed my view into “Model” view which I can select it from the side bar on the left side of the tool. The other 2 buttons on the left side bar are the “Report” view where we can do our visualization and the “Data” view where we can view our data. Again, there is a limitation on how many rows of data is able to be previewed. So, be aware of this point.

In the “Model” view, I changed the columns names by selecting the “countrycode” table and click on the “Edit Query” button under the Home menu. It opened the Power Query Editor which I mentioned earlier. There is a function called “Use First Row as Headers” in the Home menu.

This editor gives us the data profiling view, allows us to perform data cleaning and checking through the data quality through its summary statistics and column distribution. Also, I noticed the column names have been updated accordingly.

Data Profiling

It is a process of examine the data available from the data sources, either from flat files or databases. It collects and shows statistics summary such as count, min and max values, unique and distinct value, and other informative summaries about the data.

It allows us to understand the data before we can decide the data cleaning process. More often, it is a toolbox of business rules and analytical algorithms to discover, understand and potentially expose inconsistencies in your data. This helps to improve data quality or maintaining the health of the data.

Why do we need data profiling?

Data that is not formatted right, standardized or correctly integrated with the rest of the database can cause delays and problems that lead to missed opportunities, confused customers and bad decisions“.

Totally correct. Uncovering erroneous data within the dataset helps you ensure that your data is up to standard statistical measures, as well as business rules specific. Data quality check helps to identify incorrect or ambiguous data and provides ideas on what are the data to be cleaned.

You can check whether the data is consistent and formatted correctly through some pattern matching on the data types for example. Pattern matching helps you to understand whether the field is a text or number based field.

Lastly, relationship discovery. You want to know how each data sets are related or connected to one and another by starting with metadata analysis to determine key relationships.

End of the day, you will get a dataset diagram which looks similar to the screenshot once the data is cleaned, integrated and correctly formatted.

Data Visualization

The story did not end here, data visualization comes in when we have done some data crunching so that the data can be useful for analysis and visualization. Power BI data visualization covers a number of different charts, graphs and etc. It can be easily be located from its marketplace.


I added a histogram from the marketplace and pulled out some data from my dataset to see the age distribution and worth in billions distribution. It is quite interesting to look at data in this histogram and start asking questions from it.

Scatter Plot & Line Chart

Scatter plot is type of plot diagram which showing the pattern of the resulting points revealing any correlation present. A line chart or line plot or line graph is a type of chart which displays information as a series of data points called ‘markers’ connected by straight line segments. It is a basic type of chart common in many fields.

There are plenty of different charts and graphs can be used in Power BI. The important question here is knowing what kind of charts and graphs to be used in data visualization. It is important to choose the right one to draw the visual (chart) for data presentation.

%d bloggers like this: