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

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