Practical Excel & Power BI Learning Resources

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

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