Excel’s Power Query: Data clean up without formulas & Microsoft updates Webinar
14 July, 2020 2 PM London Time (BST)
Founder - trainer - data scientist - MVPdavid@xlconsulting-asia.com
14 July 2020, 2pm London time (BST)
CLICK ABOVE TO REGISTER
Our regular Tech-on-Tuesday webinars continue through the summer with more Wow’s from David Benaim showcasing the latest updates and features to Microsoft 365. Each month we will start with a round-up of what’s new with a more detailed review of some of the most recent features Microsoft has released.
Join us for our first session this month on Tuesday 14 July at the usual time of 2 PM London Time BST, David will do a deep dive into Excel and showcase how Excel can pull historic stock and currency information, better integration between Power BI and Excel plus some updates to Teams online meeting features.
More about this webinar
Anyone analysing data understands that 80% of the work is in the data clean up, but it doesn’t have to be that way. 2015 saw the biggest change to Excel in 20 years. Power Query is a tool built into Excel built to Extract data from various sources and reshape it.
To put things into perspective, nowadays David spends around 40% of his Excel time within Power Query. As an Excel MVP running a consulting firm specialising in Excel, David couldn’t imagine creating a solution anymore that didn’t make use of this no-formula way to transform your data.
Power Query allows you to:
- Folder to table: Combine Excel files in one folder into one single table
- Unpivot: Think a table with columns for Jan, Feb, Mar, Apr, etc. that you want to analyse. Pivot tables & SUMIFS won’t let you easily get a total for Q1, unless you use Unpivot in Power Query first
- In list 1 but not list 2: A common problem accountants regularly face
- One-click refresh: When the data changes, maybe a new file in a folder, a new month to unpivot or extra rows to test for list 1 vs 2, etc. Power Query’s prewritten steps are applied to the new data.
Excel’s final frontier used to be VBA, the programming language that enables you to change the rules of how Excel operates, Power Query allows you to replicate about 60% of the things people historically used VBA for, but without any code. Power Query is also featured in Power BI in the same way as Excel, and you can explore it more with David’s intro video here: https://youtu.be/TNtn-eYpNmQ
Join us for our first session on Tuesday 14 July
at the usual time of 2 PM London Time BST.
Click above to register for this webinar