Data Training from Zomalex

Home | Power BI | Excel | Python | SQL | Visualising Data | Generative AI | Analysing Data Course - Home

Tabular Data

Most of the data that we analyse will be tabular i.e., arranged in a table with rows and columns.

Different data professionals have different names for (nearly) the same thing and this may sometimes lead to confusion.

• dataset, table, query
• column, field, variable, attribute
• row, observation
• quantity, measures – numeric columns, usually aggregated

Before starting on our data modelling process, we consider the characteristics of the data. We normally analyse each variable (column) of the data in several ways

Characteristics of columns

We can describe the set of values in a columns in many useful ways.

DataType

The datatype contains the values that can be placed in the column. Common datatypes are

• numeric (either whole numbers (integers) or real numbers (floats),
• date and/or time,
• boolean,
• text.

Text: categorical or ordinal

Categorical variables represent distinct categories or groups. A categorical data can take a small set of values e.g. journey type, crime category.

We can split categorical variables into nominative and ordinal types. Nominal variables have no inherent order, for example,

• types of fruits: apples, bananas, oranges
• German car makes: VW, BMW, Audi
• countries in the United Kingdom

Ordinal variables have a meaningful order, for example,

• education levels: school, university, masters
• body mass index (BMI) classification: underweight, normal, overweight, obese
• days of the week (and months of the year)

The x axis of this chart is categorical column

Natural Grouping or Hierarchy

A hierarchy is a set of columns going from broad to narrow categories. There are often natural groupings or hierarchies in the data, such as:

• geographic regions (for example, countries, states, cities)
• organizational structures (for example, division, departments, teams)
• date hierarchy (year, quarter, month, day) They often form the basis of a guided navigation path from summary to details.

Numbers: Discrete or Continuous

Numbers can be classified into discrete or continuous types. (Categorical and ordinal variables are always discrete.)

Discrete data consists of distinct, separate values, often counted in whole numbers, for example

• number of children in a family
• number of goals scored in a football match

Discrete data is typically counted.

Continuous data can take any value within a given range. A continuous variable can take an infinite set of ordered values. Continuous variables are typically measured rather than counted, for example

• revenue,
• temperature,
• number of journeys

Use a column chart (histogram) of the count of binned ranges to see the distribution.

Numbers: Is zero important? Can numbers go below zero?

Determine whether zero holds a specific meaning in the context of the data. For example,

• in the case of revenue, zero might indicate no revenue. (These are called ratio scales)
• in temperature, zero Celsius represents a point on a scale where water freezes. (These are called interval scales)

Consider whether a variable can have negative values. This is common in financial data (for example, profit and loss) but less common in measures like height or weight.

Dates: Is time important?

If the dataset includes dates (and it usually does), consider whether the specific time of day is relevant to the analysis.

Mandatory values. Missing vs not applicable values

Often columns are constrained so that every row must have a value - no row can have a blank or NULL value. Ideally this is the case for most columns since it makes life simpler when we don’t have to consider blank values.

Hints:

• Consider whether the values in a column are mandatory or can they be optional i.e. certains rows may have missing values.
• Analyze how missing data is handled, distinguish between genuinely missing versus not applicable.

Uniqueness

Some columns must contain values that are unique. There can be no duplicate values.

Cardinality

The cardinality of a column is the number of distinct values in the column. Columns with few distinct values are low cardinality. Columns with many distinct values are high cardinality. Power BI is more performant with low cardinality columns.

Examples of a low cardinality column could be

• temperature setting (3 distinct values: low, medium, high)
• Size (5 distinct values: tiny, small, medium, large, huge)

Examples of a high cardinality column could be

• passenger full name
• date of birth

Surrogate keys

Surrogate keys are synthetic values, often integer,s used for the primary key. These are useful when there is no natural primary key and often created by large IT systems.

For example, some systems use a date key, in a YYYYMMDD format e.g. 20250312 rather than a date e.g. 12-March-2025.

The grain of a table

When creating a table, it is important to be able to articulate what the grain is. What does a single row in your table represent? Can you define this clearly?