close

Newsroom

Excel’s Power Query: Data clean up without formulas & Microsoft updates Webinar

1st July 2020

MGI World Microsoft 365 image with text overlay

Our regular #tech-on-tuesdays 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 what’s new with a more detailed review of some of the most recent features Microsoft have 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.

Join us each month on Tuesday’s

Microsoft Update Webinar with David Benaim

This month’s topic:  Excel’s Power Query - Data clean up without formulas

Tuesday 14  July, 2  PM London Time BST

Click here to register for this webinar


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:

  1. Folder to table: Combine Excel files in one folder into one single table
  2. 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
  3. In list 1 but not list 2: A common problem accountants regularly face
  4. 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 next week, Tuesday 14 July at the usual time of 2 PM London Time BST.

Click here to register for this webinar

Your webinar host:

David Benaim MVP 
Chartered Accountant, Founder & Data Scientist 
Xlconsulting

David Benaim MVP, corporate trainer & founder of Xlconsulting. David began his career as a chartered accounting training contract at Deloitte London and now runs a data analytics & training firm Xlconsulting in Cambodia which has trained over 1500 people and consulted over 100 clients in six years. David co-chairs the data residents Cambodia group, coaches speakers for Nerd Night, is also and a written & video blog author for UK’s chartered accounting institute. He has a YouTube channel with over 100 tech tutorial videos, some featured on the Microsoft Creators YouTube Channel and in February 2020, he was granted the prestigious Microsoft MVP award.

For more information please contact Paul Winder at paul@mgiukarea.com or Maxine Brock at maxine.brock@mgiworld.com.

MGI Worldwide with CPAAI is a top 20 ranked global accounting network and association with 10,000 independent auditors, accountants and tax experts in some 460 locations in almost 100 countries around the world.

Read more on Member news

MGI World #tech-on-tuesdays webinar background image for Latest Tech Update webinar

Don’t miss our next #tech-on-tuesdays webinar on 11 August ‘Hidden Outlook Tricks For Regular Emailers & Microsoft Updates’

4th August 2020

Register now for our #tech-on-tuesdays webinar on 11 August at the usual time of 2 PM BST London...

MGI World Florence landscape background image with MGI Vannucci and Member of MGI logos overlaid

Italy’s The Florentine magazine publishes Investors Visa Program feature article, written by MGI Worldwide with CPAAI member firm, MGI Vannucci & Associati

31st July 2020

In a recently published article by international online newspaper, The Florentine, global...

MGI World Xoftware as a Xervice XAAX logo

Looking for ideas on how to reengage with clients post-pandemic? Watch MGI Guerra's NEW video promoting its outsourcing services and latest digital accounting technology.

16th July 2020

Ecuador-based MGI Guerra & Asociados, of MGI Latin America shares their new promotional video...