Microsoft Power Query = Extract and Clean your data

Microsoft Power Query Connections

Good decisions require good clean data

What is Microsoft Power Query?

Quite simply Microsoft Power Query is an amazing part of Excel and Power BI that everyone should know about.

Plain Speaking:   Power Query is the worlds’ greatest washing machine!  Get all of your “dirty” data from any location, clean it up via a user friendly interface and then load it all neat and folded to a destination of your choice (Excel or The Data Model)

In Excel 2010 and Excel 2013 Power Query was a an add-in.  In Excel 2016 Power Query was built in to the Data tab in Excel and re-named Get & Transform.

Technical Talk:   Power Query is a powerful ETL utility built into Excel 2016 designed to Extract data from multiple sources, Transform the data into a layout suitable for analysis and then Load it into Excel Tables or Power Pivot.

Key advantages of Power Query in Perth

There is almost limitless potential for Power Query to assist in extracting data from your systems and transforming it into the layout you need.  Here are just a few basic examples but highly complex transformations are also possible:

  • Extracting: 
  • Link into your accounting system, HR system, Excel files etc. to grab the data you want
  • Pull data from different data sources such as AX, SalesForce, CRM Online, Access, Excel, Sequel Server and many more then shape into single or multiple data sets
  • Transforming:
  • Split complex account structures into separate columns and merge account descriptions with account codes
  • Pull data from 50 Excel files then re-arrange the data from those files into a single table
  • Consolidate and clean up the data from a single or series of CSV files
  • Loading:
  • Your clean data can simply be loaded into Excel and then refreshed at the click of a button
  • Alternatively you can load the data into Excel’s Data Model for more advanced data analysis and modelling
  • Power BI Desktop:
  • Get and Transform is also built into Power BI Desktop so by learning it you can immediately apply your knowledge to another software solution.

“Jeff and his team at Access Analytic have the ability to take complicated information and make it meaningful and useful. They know more about using Excel than anyone I know and they can either do the analysis for you, build tools so you can access the information easily or train you to do it yourself. If you need to better understand the numbers, then Jeff can help.” David Beard, Keynote Speaker, Consultant & HR Specialist

If you’d like to see how your organisation could benefit, we can help you achieve this quickly and at a very reasonable investment that will pay for itself in a short period of time.

Benefits of Power Query in Perth

Our clients often experience benefits such as:

  • Saving days of time manually manipulating messy data
  • Discovering ways to improve productivity and efficiency
  • Making better decisions that are based on facts, rather than opinions

What could these kinds of discoveries mean for your business?

Take the next step and access your data seamlessly today with Microsoft Power Query in Perth.

Contact us on +61 8 6210 8500 or by email to arrange a confidential meeting or leave your details below.

What other sources of information are available?

Take a course in Power Query

 

 

Power Query articles | Power Query books