Category Archives: Power Query

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

Convert numbers of different digits into text of fixed digits using #PowerQuery

This is an extension of the previous post. Here’s the task: If the data is coming from a CSV file, the solution is super easy. You may download the sample CSV file to follow along.

Rate this:

Posted in Power Query | Tagged , | 2 Comments

How to replace value with a value from another column in Power Query

It is a common task to replace a value with another value in Power Query. It can be easily done with “Replace Values”. However if you want to replace a value with the corresponding value in another column, it’s not … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Append tables with inconsistent column names with #Excel #PowerQuery

In the previous post Rename column names in a dynamic way with #Excel #PowerQuery, I talked about the key steps involved to rename column names in a dynamic approach; and the inspiration as well as the thinking process. As mentioned at … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Rename column names in a dynamic way with #Excel #PowerQuery

Situation: We have many different tables to be appended. It should be a simple task with Power Query. You may refer to my blogpost here for the basic of appending tables with Power Query. Nevertheless, life could be challenging in … Continue reading

Rate this:

Posted in Power Query | Tagged , | 2 Comments

Data Cleansing is never easy, even with #Excel Power Query

Don’t make me wrong. I am not complaining Power Query at all. If you have followed me for a while, you should know that I am a big fan of Power Query indeed. It is simply powerful! With Power Query, … Continue reading

Rate this:

Posted in Excel Tips, General, Power Query | Tagged | Leave a comment

Combine different ranges of data across worksheets in #Excel using Power Query

In the previous blogpost, I showed you how to use INDIRECT to prepare a summary table based on values from different ranges across worksheets. In this post, I am going to show you how to do that with Power Query. … Continue reading

Rate this:

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

Combine files of (slightly) different layouts in a folder with Power Query in #Excel

Part 2 – What if we have inconsistent table structures across files? Note: this is a long post. 😉 This is the continuation of previous post. Suggest you read that post first if you are new to Power Query. Moreover … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Combine files in a folder with Power Query in #Excel

Note: This is a long post… 😉 Part 1 – What Power Query does for us in the process? If you know about Power Query, you should know that combining files in a folder is a powerful yet simple task … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

#Excel #PowerQuery Challenge – Add value to CSV data in cells

As always, a picture tells thousand words. The above image explains clearly what we want to achieve, with Power Query in Excel of course. This problem was bought to my attention when I watched the Dueling Excel Podcast #192 by … Continue reading

Rate this:

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

Combine all visible worksheets from multiple #Excel files in a folder

It is a common task for us to combine all worksheets in a workbook. It could be a time consuming task without Power Query. With Power Query, it’s piece of cake. 😁 Wait? What about if you want to combine … Continue reading

Rate this:

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

Quick tip – Zoom in and out of the #PowerQuery editor

In both #Excel and #PowerBI Did you know… we can ZOOM IN or zoom out everything but ribbon in the Power Query Editor in both Excel and Power BI Desktop… … by pressing CTRL+SHIFT+= CTRL+SHIFT+- respectively. The story behind the … Continue reading

Rate this:

Posted in Excel Tips, Power BI Desktop, Power Query | Tagged , | 2 Comments