Practical Excel & Power BI Learning Resources

Learn the difference between Excel’s CLEAN and TRIM functions, when to use each one and how to combine them correctly. Download the free workbook to practise with the included examples.

Confused about Power BI workspace roles? Learn what Admin, Member, Contributor, and Viewer can do with simple explanations, real-world examples, and a quick quiz.

When connecting data in Power BI, you may come across terms like Import, DirectQuery, Dual, Hybrid, Direct Lake, and Live Connection. At first, they can sound like six different ways of doing the same thing. But they actually solve different problems. Some determine how Power BI stores or accesses data. Some combine multiple storage behaviors. And Live Connection is slightly different again—it describes how a report connects to an existing semantic model. So how do you know w

In this tutorial, I’ll show you how to build an interactive Excel sales dashboard using PivotTables, PivotCharts, slicers, and timelines. We’ll start from raw sales data, review the dataset, plan what the dashboard should show, create the supporting PivotTables, and then turn everything into a clean dashboard. You can download the practice file below and follow along with the video. Download the practice file: Watch the full tutorial: Dashboard scenario In this example, we ar

Learn how to use INDEX MATCH in Excel to build a reporting template that turns a full system export into a clean manager report with selected KPIs.

PivotTables are one of the most useful tools in Excel for summarising and analysing data quickly. But if you are new to PivotTables, they can feel confusing at first. You may wonder where to place fields, why Excel sometimes uses SUM and sometimes COUNT, or how to turn a PivotTable into something useful for real analysis. That is why I created this PivotTables Tutorial Series. In this series, I walk through the fundamentals step by step using a simple sales dataset, so you ca

Excel suddenly showing numbers instead of letters? If your Excel suddenly looks like this: Columns are numbers (1, 2, 3…) instead of letters (A, B, C…) Cell references show R1C1 instead of A1 Don’t worry — your Excel is not broken. It may have switched to Excel R1C1 reference style. ⚡ Quick Fix (Back to Normal in Seconds) To switch Excel back to A1 style: Go to File Click Options Select Formulas Uncheck “R1C1 reference style” Click OK ✅ Your Excel will go back to normal immed

Excel can already filter text that contains a keyword…but what if you didn’t have to do it manually every time? For example, your data might include values like “Apple”, “Green Apple”, or “Apple Juice”. While the standard filter can find these, you need to reapply it each time — and it doesn’t update dynamically. In this guide, you’ll learn how to create a more flexible solution using an Excel filter that contains text formula, so your results update instantly based on a keyw

Comparing daily data can be misleading. For example, a Monday usually behaves differently from a Sunday, so comparing them directly may lead to the wrong conclusion. A better approach is to compare the same weekday across different weeks. For example, comparing all Wednesdays in the past few weeks. In this post, I’ll walk through 3 different methods to compare the same weekday across weeks in Excel. Each method can work well depending on the workflow and reporting needs. Thes

Creating a comprehensive calendar table is crucial for time-based analysis in Power BI. Here are 15 essential DAX functions to enhance your calendar table: ShortDay ShortDay = FORMAT('Calendar'[Date], "ddd") Displays the day of the week as a short abbreviation (e.g., Mon, Tue). DayOfMonth DayOfMonth = DAY('Calendar'[Date]) Extracts the day of the month (1-31) from the date. StartOfWeek_Mon StartOfWeek_Mon = 'Calendar'[Date] - WEEKDAY('Calendar'[Date], 2) + 1 Returns the da

Transforming raw data from the left to the one on the right where the data is split nicely into each column. Let's look at the raw data, the employee shifts are all sticking together without space or any delimiter, but there is a consistent number count that can be split. However, there is another challenge where an employee may have varying number of shifts, sometimes 3 sometimes 4 or maybe 5. So I'm going to show you on how to dynamically split the data even if the number o

Having leading zeros is crucial when a standard data length is necessary. Here is a sample Calendar table featuring columns for Date, Year, and Month. The task is to prepend a zero to single-digit numbers while keeping two-digit numbers unchanged, so that 1 becomes 01, while 12 stays as 12 since it already has two digits. Here is my solution: Click on Custom Column at the Add Column tab. Using the M code below, replace the Month with your own column. =Text.PadStart(Text.From(

Below is a screenshot of time in Second format and in Minutes format. I'm going to show you to convert them into mm:ss format. Second to MM:SS format: First, we need to convert the second to day, there are 86400 seconds in a day. So, what we need to do here, is to divide Second with 86400. After dividing second with 86400, we will get decimal value. At the format panel in Home tab, click on More Number Formats... Then select Custom > mm:ss format > OK. We are done converting

Transforming table on the left to table on the right. Let's look at the blue table below first, it can be seen that the Date column (to be pivoted) has duplicates while the Store ID (index column) has duplicates as well. Note: Index column is the columns we want to group by and will remain unchanged during the pivot. Here’s the final result that is desired where the Store ID (index column) remain to have the duplicates, while the dates became the column headers, and the data

When appending tables in Power Query, the M code used looks something like below: =Table.Combine({Table1, Table2, Table3}) The problem here is if sometimes we only have Table 1, sometimes all three tables are available, it's not flexible to append these tables. For example, if we only have Table 1 and 2, we will get error like below which tells us that the table can’t be found. This makes it difficult to append if the number of tables to be appended is always changing. So her

Here's an example of ever changing headers where the dates are the headers of the data, basically means the headers change every month or day. Transforming such data in Power Query can be challenging as Power Query grabs the headers of the data when transforming the data, so whenever the headers change, Power Query can't recognize the data and gives out an error like the image below. The issue can be solved by a solution I called it: Grouping the headers/columns. So here's I

Here's an example of a Pivot Table, and I want to automatically filter the past 30 days whenever I update the raw data and refresh it. So here's the sample raw data of stores sales for the above Pivot Table from May 2024 until June 2024. To make it filter the past 30 days data in the Pivot Table, this is how I do it: At the raw data: Add an additional column to check on whether the date is within the past 30days period. The Formula goes like this: =AND([@Date]>=(TODAY()-30),[

Imagine your boss giving you a dataset like below table and wanting you to include it in the Power BI dashboard and at the same time connecting with your other existing data sources, it would be a headache. Power BI couldn't recognize and read the dataset with multiple headers and merged columns. Alright, let's get into work on cleaning this data! Let's identify the problems first~ The first problem is that this table has multiple headers. Second, this table has merged header

Calendar table is pretty useful as it can link different data sources to produce a consolidated reports or dashboards. For example, in the image below, the calendar table is linked to both transaction data and return data; therefore, both transaction and return for certain product for particular period can be seen in a visual for comparison. Creating dynamic calendar table where the calendar can be automatically updated based on the date information in the raw data source whe

Before diving into the steps for date formatting, we need to understand what is locale. Locale is used to display and identify the data type especially for data such as date, currency and numeric data, different locales have different ways of displaying data. For example, The United States is one of the countries that use MM/DD/YYYY, while in Malaysia, the date format is DD/MM/YYYY. Microsoft Excel or Power Query by default is using the regional setting in your operating syst