Category Archives: Power Query

Unstack uneven data across columns with #Excel #PowerQuery

Posted on June 5, 2019 by

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

Posted on May 13, 2019 by

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 , | 4 Comments

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

Posted on May 4, 2019 by

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

Posted on May 1, 2019 by

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

Posted on January 1, 2019 by

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…

Posted on July 26, 2018 by

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

No more Copy and Paste to combine tables with #PowerQuery

Posted on June 28, 2018 by

Basic of Append Query in #PowerQuery With no doubt, combining multiple tables (mostly on different worksheets, or even in different files) into a single “master” table for further analyses is one of the most tedious tasks we deal with Excel … Continue reading →

Rate this:

Posted in Power Query | Tagged | Leave a comment

#PowerQuery makes advanced VLOOKUP so easy!

Posted on June 21, 2018 by

Do you use VLOOKUP?  If you do, you should know there are limitations of VLOOKUP.  Also there are some cases VLOOKUP is not straightforward and efficient. Consider the following cases: What we want to achieve is to map the corresponding … Continue reading →

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Maintain a price book in #Excel with #PowerQuery

Posted on May 31, 2018 by

Feel the Power (Query) This is my first post devoting to my recent love for Excel – Power Query.  🙂 First, let me make myself clear.  I am still learning Power Query (indeed I am still learning Excel too).  The … Continue reading →

Rate this:

Posted in Power Query | Tagged , , | 4 Comments

Data transformation with Power Query

Posted on November 11, 2017 by

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

Posted on April 29, 2017 by

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