- 2,041,728 hits
- Follow wmfexcel on WordPress.com
Want Google to translate this page?
Top Posts & Pages
- When unhide row doesn't work...
- Show number in thousand (k) or in million (M) by using custom format
- How to sort bar chart in descending order?
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- Insert a text string into a specific position of another text string in #Excel
- Open an embedded Excel file during a slide show in PowerPoint?
- Perform VLOOKUP with 2 lookup values
- 2D SUMIF with two variables – one on column and one on row
- Filter a list of items from a long long list
- Dropdown calendar in Excel
My YouTube Channel
- My Tweets
Category Archives: Power Query
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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