[Affiliate]
Blog Stats
- 3,028,942 hits
-
Join 3,888 other subscribers
- 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
- How to replace value with a value from another column in Power Query
- 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
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Rename column names in a dynamic way with #Excel #PowerQuery
- Open an embedded Excel file during a slide show in PowerPoint?
- Send a cell content to Google search
- Perform VLOOKUP with 2 lookup values
My YouTube Channel
My Facebook page
- My Tweets
Tag Archives: Power Query
Power Query Challenge – Simple basket analysis
The challenge – How many transactions with just Brand A and B together? This is a common request in retail business to analyze massive amounts of transactional data. A transaction table usually contains a lot of records. As you see … Continue reading
Basic Date Calculations – Power Query vs Excel Formula
It is quite common to perform date-related calculations when we work with Excel. Before Power Query, we must do it with built-in functions. However, we may not get the results we need with a single function most of the time. … Continue reading
Posted in Formula, Power Query
Tagged CEILING, CHOOSE, Date, DAY, EOMONTH, LET, MONTH, Power Query, SWITCH, TEXT, TODAY, YEAR
Leave a comment
Convert text date into date with Power Query for #Excel
I have a couple of blogposts about how to convert dates stored as text in various formats into real date that Excel recognizes, and you can further work on it. There are many approaches such as text to column and … Continue reading
Power Query Challenge from Excel On Fire
Split columns dynamically I came across this challenge from Excel On Fire. This challenge just happened related to the topic of my previous post regarding splitting column using Power Query. So i take the challenge and offer a different solution. … Continue reading
Data Type is NOT formatting in #PowerQuery
Let’s think about this scenario. In the first encounter of Power Query, you were presented with demonstrations that show all the powerful transformations done by Power Query. You were surprised and amazed with the demos. Normally we did not pay … Continue reading
Getting Start with #PowerQuery in #Excel
As mentioned in the previous post, the awareness of Power Query is extremely low. In my experience, the hurdle for people who have learned Power Query but not feeling comfortable in using it is the fact that they are not … Continue reading
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
Do Not input “-” for zero. Use proper formatting in #Excel
Do you input a hyphen to represent zero? If you do, please stop doing so in the future. The proper way of displaying “-” for zero is to apply relevant cell format to it. E.g. Using Accounting format with no … Continue reading
Posted in Excel Basic, Excel Tips, Format
Tagged Custom Format, Format, Power Query, Replace Values
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
Filter: It’s easy and not easy
Applying filter in Excel is a very common task. We used to filter a dataset and focus on a subset of data that we are interested in. It is super easy to filter. The problem is, are you sure you … Continue reading
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
#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
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
My first video with English Voice Over
Power Query – Full Outer Join Revisit Part 2 If you have been following my blog for a while, you should probably know that I’ve started my YouTube channel about one year ago. Up to this moment, I’ve uploaded 54 … Continue reading
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
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
wmfexcel 