Work smarter by Mastering Functions in Excel My name is WONG, Ming Fung. I have keen interest in Excel. That’s why I registered wmfexcel as the site’s domain. The tagline “Work smarter by Mastering Functions in Excel” then follows to make it sound more appealing, hopefully.
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 here before you continue.
When we have two columns, like the screenshot below, it’s getting a bit complicating… but still very easy with Power Query. It just requires a few more steps.
The steps involved are:
Load the data to Power Query
Make two branches of the query loaded
Branch Query 1 is for split column A into rows
Add an index column as row identifier
Branch Query 2 is for splitting column B into rows
Add an index column as row identifier
Merge the two queries using the index column as mapping column; and Left Outer join
Expand the result and get the values
Remove the index column
Load the result to worksheet
Relax and Refresh (when data changes/updated)
As promised, here’s a video to demonstrate the above steps.
Split cells with line feed into rows with Power Query - YouTube
If you have already read the previous post and would like to jump to how to deal with two columns, please start watching from 03:33.
As you see, Power Query is such a powerful data cleansing tool that every serious Excel user should know about.
You may download the sample files to following along.
You may be thinking… what if we have more columns of this kind of “dirty” data. The above method may work fine when we are dealing with 2 columns, or three columns at most. When it comes to 5 columns, or even 10 columns, the method described could be really tedious and time consuming. I totally agree with you. Nevertheless, I don’t have a solution for that. If you know or have any suggestions, please share with us by leaving comments.
Webinar 2 – Excel Dashboard for Excel 2010/2013/2016/2019/Office 365 using Power Query and Power Pivot
Excel is evolving; in the last couple of years Microsoft have added new tools like Power Query and Power Pivot, to name a couple.
Embracing these new tools will not only enable you to get your work done more quickly, but also since less than 1% of Excel users know these tools you’re going to have a huge competitive edge in the workplace.
It’s easy to ignore these developments and continue doing things the way you always have, but that won’t get you ahead in your career, but you know that right?
If you have Excel 2010, 2013, 2016, 2019 or Office 365* then this webinar will showcase how you can use Excel’s Power Query tool to get data from multiple sources, mash it up with Power Pivot, analyse it in PivotTables to create this interactive Excel Dashboard.
*Power Query is available with all versions of Excel 2010, 2013, 2016, 2019 and Office 365. Power Pivot is available with the desktop version of Excel 2010 and Excel 2013/2016 Office Professional, Office 2019, Office 365, or in the standalone versions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.
Over 35,000 people have attended Mynda’s Excel Dashboard webinars and this is what some of them had to say:
“Thank you! This webinar has already saved tremendous time and effort. I tried some of the techniques you illustrated for a cumbersome report update that I can see will cut our preparation time by 2/3’s or more. ….awesome! Many thanks!!!!”
“WOW, just WOW
Can’t type more now – am trying to create a dashboard. Thank you Mynda!”
“The webinar was fantastic! I really enjoyed every moment. As you said, it was fast paced, but I am so excited to go back and review the presentation. We have been trying to get a dashboard started for my department and this really motivated me to take the lead. Thank you so much!”
Dashboard Course Format
If you’ve seen Mynda’s free Dashboard Webinars and you’re ready to take your dashboard skills to the next level then check out Mynda’s Online Excel Dashboard course.
It will teach you how to build amazing interactive dashboards that impress your boss, get you noticed and help you stand out from the crowd. The best part, aside from the praise from your boss and colleagues ;-), is you’ll also learn loads of tips you can use in your everyday Excel work to get things done more efficiently.
I highly recommend the course, but don’t take my word for it. You can read further comments from past students and find out more here.
Feel free to share this to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinar too. They’ll thank you for it.
Disclosure: I make a small commission (at no additional costs to you) for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.
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 folder?
Will you do it manually, by eyeballing? @_@
The first challenge is to get the list of files you have in the folder. The second challenge is that fact that you will receive files and put them into the folder from time to time. In other words, the folder content is changing. You absolutely want a dynamic solution that helps you to monitor status at ease, so that you know which files are missing.
Here comes Power Query to rescue.
Note: All the screenshots are coming from Excel 365. If you are using Excel 2010/2013, Power Query has its own tab on the ribbon. Moreover, you need to download and install Power Query for Excel as add-ins. You may download Power Query for Excel in the following link:
If you are using Excel 2016 or later, Power Query is already built in and resides in Data tab, but renamed as Get and Transform.
The following is the step by step approach to solve the problem.
A. Convert the list of files in an Excel Table
Select a cell of the data range, then press Ctrl+T to create an Excel Table
Check the data range is correct
Check “My table has headers”
B. Load the Table to Power Query
Go to Data Tab
Click “From Table/Range“
Now you should have the Power Query Editor opened.
Note: The Power Query Editor is where all the magic happens. Nevertheless, the intention of this post is not to explain in details all the magical stuff in Power Query. Hence please follow the steps and focus on the solution to the problem stated.
Rename the query. As a good practice, always rename your query to something meaningful. In our case, it’s renamed as “FilesRequired“. That is the files we are expecting in the designated folder.
C. Load the list of files in the designated folder to Power Query
On the Home tab of Power Query Editor
Click the pull down menu of New Source
Another dialog box opened. From there,
Either browse to the folder path, or input directly
In just a second, you should see the following.
Simply click Transform Data
Note: If you are using Power Query for Excel 2010/2013, the button “Transform Data” is labelled as “Edit”.
Now you should have the second query loaded to Power Query Editor. This query showcases attributes of all files in the designated folder.
Rename the query to “FilesReceived“
A quick recap here:
By now, you should have two queries loaded to Power Query Editor. And the queries are
FilesRequired – From the Table on the spreadsheet, i.e. the list of files you are expecting
FilesReceived – From the Folder, i.e. the files you’ve got in the designated folder
D. Merge the two queries with Left Anti Join
Now we are going to compare the Query FilesRequired to FilesReceived, in order to identify the files that are available in FilesRequired, but not yet appeared in FilesReceived. This can be achieved by merging the two queries.
On the Home tab of Power Query Editor
Open the pull down menu of Merge Queries
Select Merge Queries as New
In the subsequent Merge dialog box:
Click the header “Files Required” under FilesRequired
Flash Fill was introduced in Excel 2013. After 6 years, it is still a hidden gem of modern Excel that most regular Excel users do not even know its existence. Whenever I show to my colleagues what Flash Fill is capable of, they are all impressed and think that this is their “Greatest Discovery” moment.
So in this post, I am going to show you in a flash what Flash Fill can do with 16 examples.
Note: CTRL+E is the keyboard shortcut for Flash Fill which had been used in all the GIFs in the coming demonstration.
Example 1 – Extract First Name and Last Name where coma is the separator
Example 2 – Similar to example 1 with no delimiter but CamelCase
Example 3 – Get the 3rd (or Nth) value from CSV
Example 4 – Separate Text from Value and vice versus
Example 5 – Swap the position of First Name and Last Name
Example 6 – Replace the coma with Space
Example 7 – Add Initiate in the middle
Example 8 – Change all texts to lower case
Example 9 – Change all texts to UPPER case
Example 10 – Change only Last Name of UPPER case
Example 11 – Get Initiate, and then add it as a prefix
Example 12 – Get Initiate, and then add it as prefix without the helper step
Example 13 – Get Initiate, and then add it as suffice
The following examples involve more than one pattern. It requires you to “teach” Excel with more correct examples.
Example 14 – Swap First Name and Last Name without coma, and add initiate as suffice
Example 15 – Getting value from the inconsistent pattern
Example 16 – Getting value from the super messy pattern
Are you convinced now Flash Fill is simply amazing? However, in order to have Flash Fill worked well, you need to:
Study your data and find the pattern
Flash Fill works well when there is a consistent pattern
Input examples next to the data (table)
Review the suggested results
“Teach” Excel with correction, when the suggestion is not correct
ALWAYS check your results, esp. when there is more than one pattern
Last but not least, explore Flash Fill on your own!