I recently started learning about Tableau, a business intelligence (BI) tool that helps you visualize data. I won’t get too deep into that, but it is relevant to how I stumbled upon Power Query, and may help me convey what it does. If you spend time adjusting spreadsheets, you need to learn Power Query. It will give you some of that time back.
Back to Tableau for a bit
So, as I mentioned Tableau helps you visualize data. The visualizations can be graphs, charts, maps, and more. The data can come from one or more of many different kinds of sources (spreadsheet, database, API, and more). When you combine multiple data sources, you have to use a tool called Tableau Prep. So for example, let’s say you have a three lists of names for an event. One for invitations (people who where explicitly invited to the event), one for registration (who registered, and when), and attendees (people that actually showed up to the event). Now let’s say you want to find out what people from the invitation list actually attended, and you want to see when they registered too. Tableau Prep can help you do that with ease. It is a pretty awesome tool too, but did I mention that Tableau is expensive? At this time, it is about $70/mo for each user.
Enter Power BI and Power Query
Since I wanted to use a tool like Tableau, but Tableau is prohibitively expensive, I looked at Microsoft’s Power BI. First to compare prices, and second to see if they have a neat tool like Tableau Prep. The price is laughably lower, like $10/mo if it isn’t already included in your Office suite, and yes it has a tool that you can use like Tableau Prep. That tool is Power Query, and not only is it included in Power BI, but it is included for free in Excel. I haven’t even gotten to Power BI yet because I’ve been enamored with Power Query.
Use Case #1 – Simple
You have a spreadsheet with a table of contacts. You want to sort by last name, but can’t, because each record is storing the full name. With a couple of clicks in Power Query, you can separate it out into first and last name so you can sort by last name.
Use Case #2 – From File
You have a sales department with a number of sales people that collect contact info for customers. At the end of each year, you want to send out holiday cards to those customers. You can have each sales person export the contacts from Outlook to CSV file, and put them into a single folder. Then you can use Power Query to merge all the files in that folder, and remove duplicates.
** Check back here in the future. I plan to implement this one, as I know someone that wants something similar.
Use Case #3 – From API
You have a website and a YouTube channel. You want to import the title, description, link, and publish date for each YouTube video into your website. You can connect to the YouTube API from Power Query to get all the info you need from each video, and format it so it can be imported into your site.
What Next?
Assuming I have talked you into giving Power Query a closer look, where should you start?
I am a fan of LinkedIn Learning, and it is how I learned. You can find the links to the courses below. The First one is only about 25 minutes, so it isn’t a big commitment. The second one is more in-depth.
https://www.linkedin.com/learning/excel-power-query-for-beginners/
https://www.linkedin.com/learning/excel-power-query-get-transform-2/