Experience is a great teacher. And in my experience, the beginning has always been tough.

As a BI consultant & trainer, I have worked with various people: from the frontline employees to CEOs. Our interactions gave me great insights into the multiple challenges they face.

I have put together a few pages from my experience and learning; covering Power BI, Excel, Power Query, Power Pivot, Tableau, data analytics & data visualization.

The purpose of the blog is to get you started.

I hope you’ll find them helpful.

Vivek

QuickBytes is a series of articles for simple and easy to implement techniques with exponential benefits


Usually, we start designing a report, dashboard, or a presentation slide with a blank white space. Each element, color, or texts we add to the page competes for attention from our viewers. It implies that we should be careful about what we put on that space. Limit the ink we use to convey our message.

Let us consider the following example—a simple table with sales details by manager and order priority.









Each element in the table is equally competing for our attention.

Compare it with the following table:









In the above example,

  • Numbers are more apparent as the borders have taken a step back and not competing with the numbers.

  • Cells with zeros appear as blanks. It makes our table cleaner and the viewers can pay more attention to the sales numbers.

  • Numbers have a thousand-separator and a different format for negative numbers.

This article explains how to hide zeros in an Excel table.


Step 1: Select a base format

Select the cells > CTRL+1 (for Format Cell) > Select an appropriate format

In the above example, I have selected Number format with a negative number format and a select Use 1000 separator(,).


Step 2: Custom Format

Go to Custom Format > Under type, we find the structure of the format we have selected.


In Excel, the number format has a separator - semicolon (;).

In the example, Excel has applied format for positive and negative separated by a semicolon(;). All we require is to add the second semicolon (;) after the negative format structure. Any structure defined after the second semicolon is for the zeros.


And if we leave it blank after the second semicolon, then that is how Excel is going to display zeros: blank.


And that is it.

Clutter is a distraction. Please get rid of it!


@imvivran

Appending multiple tables & creating date from the text columns



One of the clients came up with a small project. They wanted to quickly analyze their sales performance in three countries for the past three years. The data file shared with me was not raw data, but a country-wise monthly summary report.


Below is one of the three tables:


The other two tables are for Germany and India.


Following are significant challenges with the data:

  • Data is in three separate tables

  • Yearly data stored in three separate columns, with a prefix “Sales.”

  • The month in a separate column, in text format.

We are using Power Query to compile and transform data under two minutes!


Download Sample File


Step 1: Appending all three tables.

For this example, I would like to compile all the three tables in a separate table. Since all the tables have the same headers, our job is straightforward. We will use the Append Query function, which equivalent to Union in SQL queries.


Home > Append Query as New


In the Append Query window, select Three or more tables and add all the tables under Tables to append.


Click OK, and we get a new table with the data from all the three tables.


Right-click on the other three tables and uncheck the option Enable Load.


This step ensures only the table with all the records loads in the data model. Since we do not require the other three tables, we have excluded them from loading to the data model.

Step 2: Bring data for all the three years in one column

The sales data for three years are in three separate columns. For analysis, this is not helpful. What we need is all the sales value in one column and along with the subsequent years in another separate column. For this, we use the option unpivot.


Select the columns for the three years > Transform > Unpivot


For the next step, we need to extract the year value from the column. There is more than one way to do this. In this example, we are using the option Extract under Transform

Select the column > Transform > Extract > Last Characters


Since we need the last four characters, enter 4 in the Extract Last Characters:

Output:


Step 3: Transforming texts into a date format

Now the challenge is that we have two columns containing date components with column property as text. To meet our objective, we need one column with a date property.

Select the column Month and Year > Right-Click > Merge the column with space as delimiter


Transform the column from Text to Date















And our table is ready for analysis under two minutes.


@imvivran

QuickBytes is a series of articles for simple and easy to implement techniques with exponential benefits


I love the Data Preview feature in Power Query. As an analyst, I would prefer to get an overall summary of data. Data Preview is a great way to achieve this.

Benefit# 1: Visual representation of the overall health of the column

The bluish-green bar below the header name visually represents the health of the column:



  • A solid greenish-blue line represents non-blank values

  • A greyish-black fill represents null

  • A red fill represents the presence of errors (primarily due to invalid data conversions)

When hovering on the bar, it displays additional details:


Check the Column Quality option under View to display the details:


Benefit# 2: Column Cardinality

Cardinality is the representation of the presence of unique values in a column. The higher the number of unique values, the higher the cardinality.


For an efficient data model, Power BI experts recommend removing the unnecessary columns, especially with high cardinality. Tables with fewer cardinality columns help in better table compression, hence faster data loading and query execution.

Use Column Distribution feature to show the distribution and the cardinality details quickly:


Benefit#3: Summary Statistics using Column Profile

Getting a summary statistic of a column is one of the first steps performed in any data analytics. It helps in getting an overview of data distribution.


Enable Column Profile:

View > Column Profile







Enabling Column Profile in Power Query provides a visual representation of the distribution along with a summary statistic.

For a number column:

For a text Column:

Upon hovering, it provides additional details (count, % contribution):




We can copy-paste the summary statistics:


We can filter our data table using options available in Column Profile


Defining the Scope of Data Preview

By default, Power Query shows only the top 1000 records for preview. We can view the status at the bottom left corner of the Power Query window:


We can, however, increase the scope to the entire data set, by clicking on it and selecting changing the selection :






A word of caution: The processing time to show the Data Preview will change according to the number of records in the dataset and the processing power of the machine. So, use it wisely.


@imvivran

Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING