Blog Stats
- 2,953,775 hits
-
Join 372 other followers
- 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
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- How to replace value with a value from another column in Power Query
- Perform VLOOKUP with 2 lookup values
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Open an embedded Excel file during a slide show in PowerPoint?
- Highlight weekends and holidays using Conditional Formatting in #Excel
- Insert a text string into a specific position of another text string in #Excel
- Rename column names in a dynamic way with #Excel #PowerQuery
My YouTube Channel
My Facebook page
- My Tweets
Search Results for: Power Query
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
Extract content from a column in Power Query for #Excel
It is a common task to extract certain texts from a cell in Excel. For example, we may want to If you are good in Excel formula, you may be able to achieve this by constructing formula with Excel functions … 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
Split column in Power Query for #Excel
In regular Excel, Text to Columns is the non-formula approach to split contents from a column into multiple columns. It is a very powerful feature in Excel which many users are not yet aware of it. If you want to … Continue reading
Merge Columns in Power Query for #Excel
Merging columns is a super easy task in Power Query. What we need to know is where to click. Having said that, we need to pay attention to The order of selecting columns to be merged, as it directly impacts … Continue reading
Replace values in Power Query
Find and Replace is quite common in Excel. If you are not sure about what options we have for Find and Replace, please read my blogpost here. In regular Excel, the Find and Replace action is one-off. Whenever we have … Continue reading
Keep the rows you need with #Excel Power Query
When we import data into Power Query editor, we should set the scope of data that we need. That means selecting only the rows and columns required for further processing. In the following video, I will show you different ways … 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
How to replace value with a value from another column in Power Query
It is a common task to replace a value with another value in Power Query. It can be easily done with “Replace Values”. However if you want to replace a value with the corresponding value in another column, it’s not … Continue reading
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
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
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
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
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
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
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
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
wmfexcel 