Search Results for: Power Query

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

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

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

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

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

Dense Ranking with Power Query – Unexpected Behavior and Workarounds

This post is inspired by the article – Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub. The first part of this post is a summary of what I read from Philip’s post. While the second part is … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 2 Comments

Power Query Challenge – Joining two tables fully (Part 2)

Append Queries + Group By = Another alternative for a better Full Join In the previous post, we explored an alternative way to perform FULL OUTER JOIN with Left Outer Join + Right Anti Join, followed by Append Queries in … Continue reading

Rate this:

Posted in Power Query | Tagged , | 1 Comment

Power Query Challenge – Joining two tables fully

Left Outer + Right Anti = A better Full Join A sample file is available below. If you prefer reading to watching, please continue to read this post. The situation: I have two tables.  One is Sales Table; One is … Continue reading

Rate this:

Posted in Power Query | Tagged , | 2 Comments

Power Query Challenge from Mr Excel – Reshaping data

I really like Power Query (in both Excel and Power BI).  Honestly I do not know much about the M behind the scene (still learning it piece by piece).  Nevertheless, I’ve got the POWER simply through the User Interface of … Continue reading

Rate this:

Posted in Power Query | 1 Comment

#Excel Power Query solves a real life problem – Who eats what?

Interesting application of Power Query for real life problem – See how to transform table into different layout using Power Query I came across this Power Query Challenge from Computergaga and I found it really interesting as it seems that … Continue reading

Rate this:

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

Identify missing files in a folder with Power Query

It is a common task to compare two lists in Excel.  This can be achieved with MATCH function.  But what if you want to compare a list of filenames stored in your spreadsheet, with the files you’ve got in a … Continue reading

Rate this:

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

[Share] Introduction of #Power Query M code by ExcelIsfun

Happy New Year!  Wish you all an Excel-lent year of 2019! As an Excel nerd, I’d like to spend my holidays on learning Excel… haha… I know I am abnormal.  ;p  My recent love of Excel is Power Query (M) … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Data transformation with Power Query

Transform date input as “Excel-unrecognized” text string into date (number) usable by #Excel This is a continuation of the previous post – How to turn “1st January, 2017” into #Excel recognizable date?   …using Get & Transform (better known as #PowerQuery). Note: … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

[Guest Post] 5 things you should start doing with Power Query

This is a guest post by Puneet Gogia. Power Query is a revolution. It’s a (part of a) complete self-service BI tool which can help you in importing, connecting, shaping, cleaning and sharing your data with little efforts. It has … Continue reading

Rate this:

Posted in Power Query | Tagged | 12 Comments

The powerful Power Query and the interesting TechNet Virtual Labs

As you may be aware of, I haven’t written a real blogpost recently.  I was super busy at work in the past month.  No surprise.  There are moments Excel won’t help me much at work, especially when bosses like PowerPoint (and PDF) … Continue reading

Rate this:

Posted in General | 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