Convert Matrix data in Excel to Tabular Data in Power BI.

There is a common way of seeing data in Excel as a Matrix format; many data entries look like a matrix in many contexts, from accounting to health data. However, to better analyze data and add more calculations and charts and, in general, get more insight from data, we need to change the matrix data in Excel to a tabular format in Power BI.

For example, below table, we have data about the Deaths from Cancer of Lip, oral cavity, and pharynx by age, sex, and year.

A simple look at the data can be seen below.

The above picture shows a simple design of the former table; as you can see, we have header data at the top of the table name Age Group. Then we have another one for the Year, and then we have Males and Females.

Analyzing the data in this format is impossible in Power BI, and it prevents us from creating proper visuals and calculations.

The best format to analyze data should look like the one below: all of these header data be in a separate column.

As you can see in the below table,

we have four different columns, and the year, gender, and Age group have been repeated for all rows.

This table :

  • It does not have a Total value, as we can calculate it in the visualization easily
  • each row contains one data
  • data has repetition for better slice and dice ( Later, we can define a separate table for Year and Age group if needed)

Power Query

After importing the data into Powe Query

the first data look was as below

I removed some of the necessary rows of data and replaced some data value with the correct one so the data look like below.

There is another data transformation needed before making a data stable style.

Each year has been repeated for three values (Total, female, and Male). I have to use Fill Down features to replace null for each of those three values.

Next, I renamed the first column to Year second to Gender, and also I promoted the first row to be a header.

In next action I will remove total from Gender column as we able to calculate the total later in visualization and other calculations.

In the following action, I need to make the age group a single column as distributed as many columns at the top, and this means I need to use the Unpivote Other columns option by selecting the Year and Gender a

The final look would be as below

as we have a separate column for the Year which has many repetitions, we have a gender column, an attribute that is the age group, and the last column shows the number of death for the selected year, gender, and age group.

after loading data into visualization the slice snd dicing data is much easier and you can use variety of visuals and slicers

Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

Leave a Reply