Author Archives: MF

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.

Power Query Challenge from Excel On Fire

Split columns dynamically I came across this challenge from Excel On Fire. This challenge just happened related to the topic of my previous post regarding splitting column using Power Query. So i take the challenge and offer a different solution. … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Split column in Power Query for #Excel

In regular Excel, Text to Columns is the non-formula approach to split contents from a column into multiple columns. It is a very powerful feature in Excel which many users are not yet aware of it. If you want to … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Merge Columns in Power Query for #Excel

Merging columns is a super easy task in Power Query. What we need to know is where to click. Having said that, we need to pay attention to The order of selecting columns to be merged, as it directly impacts … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Replace values in Power Query

Find and Replace is quite common in Excel. If you are not sure about what options we have for Find and Replace, please read my blogpost here. In regular Excel, the Find and Replace action is one-off. Whenever we have … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

How to sum a range with #ERRORS in #Excel?

SUM is probably the most frequently used function in Excel. And probably the first function we learned. Though powerful, it cannot handle errors… 😰 This is the fact we need to deal with. SUM cannot handle errors. AGGREGATE comes to … Continue reading

Rate this:

Posted in Formula | Tagged , | Leave a comment

The strange behavior of SUMIF and the quick fix

Have you ever got an incorrect result from SUMIF and wonder why? You can find the answer in this post. 🙂 Continue reading

Rate this:

Posted in Formula | Tagged | Leave a comment

Basic text transformation in #Excel #PowerQuery

In regular Excel, all the above Power Query transformations can be done with simple functions, except for TRIM which Power Query does it slightly different from the regular Excel. You may read the previous post for more details. You may … Continue reading

Rate this:

Posted in Power Query | Leave a comment

Trim like #Excel in #PowerQuery

In Power Query, there are some functions that are very similar to Excel, which you expect the same results from them. However, it may not be the case because they indeed perform differently. TRIM is one of them. TRIM is … Continue reading

Rate this:

Posted in Power Query | Tagged , , , , | 6 Comments

Keep the columns you need with #PowerQuery

When we import data from a data source, be it a data warehouse, a CSV/TXT file, or even an Excel Table, there are chances that there are many more columns than needed for analysis. This is particularly true when we … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

A twist to turn Keep Duplicates into Keep Unique in #PowerQuery

In Power Query Editor, there is a button of “Keep Duplicates” in the ribbon. It’s located under Keep Rows in the section of Reduce Rows in the Home tab of the ribbon.     With this, it is super easy … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Keep the rows you need with #Excel Power Query

When we import data into Power Query editor, we should set the scope of data that we need. That means selecting only the rows and columns required for further processing. In the following video, I will show you different ways … Continue reading

Rate this:

Posted in Power Query | Tagged , , | Leave a comment

Data Type is NOT formatting in #PowerQuery

Let’s think about this scenario. In the first encounter of Power Query, you were presented with demonstrations that show all the powerful transformations done by Power Query. You were surprised and amazed with the demos. Normally we did not pay … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Close and Load… to table or to connection? That’s the question

Close and Load to is the last step in Power Query, but the first step in your reporting process. When I first learned Power Query, I preferred loading the result to Table as I could “see” the result on a … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

How to put the Date Modified info on #Excel worksheet using #PowerQuery

If we want to see when a workbook was last modified, we can see the information from File Explorer, or Excel Info Very handy indeed. However, if you want the information to be put on a worksheet, there is no … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Getting Start with #PowerQuery in #Excel

As mentioned in the previous post, the awareness of Power Query is extremely low. In my experience, the hurdle for people who have learned Power Query but not feeling comfortable in using it is the fact that they are not … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Will Power Query change your life?

Well, it depends! Depends on what? It depends on what you do and how you do it in Excel. 😉 True/False moments Excel is the core software I use for work I use Excel for data analysis I need to … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Happy CNY!

Rate this:

Posted in General | Leave a comment

Quick Tip – Edit #DAX formula in #PowerBI Desktop efficiently

Let me share a quick tip of Power BI today coz I work with Power BI Desktop a lot recently. 😎 What I am going to share in this post is my favorite shortcut key in Power BI Desktop. Honestly, … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Highlight differences in two ranges with Conditional Formatting in #Excel

Happy New Year! Wish you all an Excellent Year of 2022! To kickstart the year, let’s share a trick to solve a common task – highlighting differences in two ranges, with conditional formatting. Common enough, right? The ingredients are simple, … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Highlight different cells in two lists in #Excel

Finding and highlighting differences should be a common task in Excel. Most of the time, it’s solved by formula or conditional formatting. Either way, we need to construct a simple formula like the one below to compare the cell contents … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment