Category Archives: Power Query

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 , | Leave a comment

Split date ranges from cells with #Excel #PowerQuery

Take a look at the challenge presented above.  Is it possible to do it in Excel? Be honest, without Power Query, I have no idea how to do it but manual work. 😁🤦‍♂️ Believe it or not, with Power Query, … Continue reading

Rate this:

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

Transform and Combine all worksheets in an #Excel workbook with #PowerQuery

Power Query is magically powerful, yet it is not well known to majority of Excel users.  I think most Excel users are not even aware of its existence…  So, let’s talk about how Power Query could solve a real-life problem … Continue reading

Rate this:

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

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

So many different ways. That’s #Excel!

Last week, I posted a video and blogpost in response to a challenge from Mr. Excel .  And this week, Mr. Excel has collected viewers’ solutions and post a video to summarize different techniques used for solving the same question.  … Continue reading

Rate this:

Posted in Power Query | Leave a comment

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

Unstack uneven data across columns with #Excel #PowerQuery

This is a continuation of the previous post, in which I showed you how to unstack two columns of data by using Pivot Table as a helper, and together with a couple of excel tricks.  As mentioned, that approach is only … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Split cell contents separated by line feed into rows with #Excel #PowerQuery – Part 2

Last week, I showed you how to split cell contents separated by line feed into rows with Power Query.  That was easy when we are dealing with only one column.  If you don’t know how, you may read the post … Continue reading

Rate this:

Posted in Excel in Action, Power Query | Tagged , | 5 Comments

Split cell contents separated by line feed into rows with #Excel #PowerQuery

After an in-house training, a colleague came to me and asked if there is a way to do the following: The problem here is the contents in cells are separated by a line feed (line break).  A cell may contain … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

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

Load multiple #Excel Tables as separate #Queries quickly…

To load an Excel Table into Power Query is easy.  Just click into any cell of the Excel Table, and then click the From Table/Range button (depends on which version you are using, this button resides in different location on the … Continue reading

Rate this:

Posted in Power Query | 2 Comments