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.

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(

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

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

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

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