[Affiliate]
Blog Stats
- 3,071,735 hits
-
Join 3,282 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
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- How to replace value with a value from another column in Power Query
- 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
- Rename column names in a dynamic way with #Excel #PowerQuery
- Send a cell content to Google search
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Power Query
Got bitten by an undefined data column in #PowerQuery
The importance of explicit data type in Power Query What could go wrong when we have a column of “ANY” data type in Power Query? Well, it depends. Depends on what? Sometimes luck! 😅 This query has been working well … Continue reading
Add Index Column in #Excel using #PowerQuery
In Power Query, it is a super easy task to add a column of sequential numbers. We can do it by adding an Index Column. There are three options for adding Index Column: This can be demonstrated in a 1-minute … Continue reading
Add Column From Examples with #Excel Power Query
Use it with caution although it’s a powerful feature! In the previous post, we talked about how to add conditional columns in Power Query in which we identified the store type of a store by the prefix of the store … Continue reading
Add conditional columns with #Excel Power Query
Situation: We are given a simple table on the left. We need to add three additional columns based on the prefix of the StoreID, and the sales values. The table on the right is the expected outcome. And here’s the … Continue reading
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
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
Basic text transformation in #Excel #PowerQuery
In regular Excel, all the above Power Query transformations can be done with simple functions, except for TRIM which Power Query does it slightly different from the regular Excel. You may read the previous post for more details. You may … Continue reading
Posted in Power Query
Leave a comment
Keep the columns you need with #PowerQuery
When we import data from a data source, be it a data warehouse, a CSV/TXT file, or even an Excel Table, there are chances that there are many more columns than needed for analysis. This is particularly true when we … Continue reading
A twist to turn Keep Duplicates into Keep Unique in #PowerQuery
In Power Query Editor, there is a button of “Keep Duplicates” in the ribbon. It’s located under Keep Rows in the section of Reduce Rows in the Home tab of the ribbon. With this, it is super easy … 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
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
Close and Load… to table or to connection? That’s the question
Close and Load to is the last step in Power Query, but the first step in your reporting process. When I first learned Power Query, I preferred loading the result to Table as I could “see” the result on a … Continue reading
How to put the Date Modified info on #Excel worksheet using #PowerQuery
If we want to see when a workbook was last modified, we can see the information from File Explorer, or Excel Info Very handy indeed. However, if you want the information to be put on a worksheet, there is no … Continue reading
wmfexcel 