Blog Stats
- 2,319,121 hits
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- How to sort bar chart in descending order?
- Show number in thousand (k) or in million (M) by using custom format
- When unhide row doesn't work...
- 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
- Perform VLOOKUP with 2 lookup values
- Open an embedded Excel file during a slide show in PowerPoint?
- Dropdown calendar in Excel
- Filter a list of items from a long long list
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Power Query
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
#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
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
Posted in Power Query
Tagged Combine Workbook, Invoke Custom Function, Remove Top Rows, Unpivot column
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
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
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
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
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
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
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
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
Posted in Power Query
Tagged Conditional Column, Demote Headers, Merge Query, Pivot Column, Transpose Table, Unpivot column
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
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
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
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
[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
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
Posted in Power Query
2 Comments
No more Copy and Paste to combine tables with #PowerQuery
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
#PowerQuery makes advanced VLOOKUP so easy!
Do you use VLOOKUP? If you do, you should know there are limitations to 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
Maintain a price book in #Excel with #PowerQuery
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
