Removing a step is the undo of Power Query.Īll of these steps are saved in the query. Power Query does not have an undo button. The steps that a user has performed are recorded and shown in the Applied Steps pane.Ī user can view, edit and delete the steps from here.

But you can click View > Formula Bar to show it in the Power Query Editor.Īdvanced users can manipulate this code by using the Advanced Editor on the Home tab, or directly in the Formula Bar. Power Query shields you from the code by hiding the Formula Bar. It is quite different to Excel formulas and tricky to master. It has a nice easy to use interface with a variety of commands that will be recognizable to many Excel users.Īs users perform commands to clean and shape their data such as split columns, replace values or remove duplicates - Power Query records the step and writes M code behind the scenes (similar to how VBA is written when you record a macro). This tool is extremely powerful, incredibly useful - but yet simple to use.

New connections are being built into Power Query regularly making it easy to get data from external sources.Īfter importing data (connecting to a source) the Power Query Editor window opens. Power Query can import data from a variety of sources including CSV, text files, a folder on a drive, Facebook and Salesforce. From Excel 2016 and in Microsoft 365, it is available directly within Excel from the Data tab, but is labeled Get & Transform. It first appeared in Excel 2013 but was made available only as an add-in named Power Query.

Excel 2013 pivot table cheat sheet