As you may be aware of, I haven’t written a real blogpost recently. I was super busy at work in the past month. No surprise. There are moments Excel won’t help me much at work, especially when bosses like PowerPoint (and PDF) more than Excel. You know what I mean!
Nevertheless, because of the tight schedule and heavy workloads, I did actually spend more time in exploring the Power Query, which is simply POWERFUL.
To be honest, I am just a beginner in using Power Query. And what I used are mainly the most basic functions from the ribbon tabs (Home, Transform, Add column) of the Query Editor. But you know what, it helps a lot already. And it is indeed quite intuitive to an experienced Excel user.
What I really like about Power Query?
- Merge tables (queries) together before you start your analysis via Pivot Table – A total WIN vs. traditional VLOOKUP as it makes multiple-values lookup become a super simple task.
- Unpivot columns – does it sound a common but time consuming task to you?
- Transform data type, especially “Text Date” to real Date – another common problem in daily work
- Group by – work like a charm to aggregate data (what I normally do with a Pivot Table)
- Extract part of string from a column – focus on the portion of data you need
- And many more… but
THE MOST IMPORTANT one is
- Whatever you did, it’s recorded and refreshable!! It means once you have set it up right, all the repetitive tasks for updating the report can be done with a simple Refresh.
Where to get the Power Query?
If you are using Excel 2010 or 2013, you may download and install the Power Query Add-in from the path below:
https://www.microsoft.com/en-us/download/details.aspx?id=39379
If you are using Excel 2016, you don’t need the Add-in as it is already part of the programme. Can’t find Power Query in your Excel 2016? Of course as it is renamed to Get and Transform, under the Data tab. Why it is renamed to Get and Transform? My best guess is Get it and Transform your career. 😛
Want to learn more about Power Query?
Mynda Treacy of myOnlineTraininghub has a very detailed post on what Excel Power Query is about, together with a short demonstration, and related training courses.
Wants hands-on experiences first?
Recently I came across an interesting virtual lab which takes you through the fundamentals of Get and Transform.
You need a Microsoft account to log in.
Once you logged in, you will see a screen like this:
(Note: There is a timer on the top right corner. The clock is ticking and you have 2 hours counting down…)
It is a Remote Desktop connection (I believe). You don’t even need to have Excel installed in your computer to experience it. You can do it even on Mac, as long as you have internet connection and your machine meets the system requirement.
You will be guided to complete the following tasks step-by-step:
- Import your first text file
- Creating a PivotTable to answer our questions
- Import Data from a Database
- Import multiple files and merge queries
- Creating a PivotTable to answer our questions
- Import an Excel File
I am quite satisfied with the overall experience, although I had encountered a few “Not responding” moments. 🙂
A suggestion for improvement is the layout of the panes. Currently, the description of each task is sitting at the bottom of the screen, when you click a task. Would it be better and easier to follow through if it appears right below the task clicked on the right pane?
Aside from the layout, the thing I like most is it allows you to access to a real Excel 2016 environment. In this way, not only did I experience Power Query (i.e. Get and Transform) but also many new features like Waterfall chart in Excel 2016. I am still using Excel 2010 most of the time. 😦
TechNet Virtual Labs
It’s more than just Excel. Check it out when you have time.
wmfexcel 