The command is flexible, allowing you to specify something such as keep row 2, delete the next three rows, then keep one row, and follow that pattern through the data. Remove Rows: If the incoming data is double-spaced, the Remove Rows menu has an option to Remove Alternate Rows. Here are a few of the tools and features in Power Query that are better than their counterparts in Excel.įill Down: If you have a column that’s in outline form-for example, “East Region” is in cell A2 followed by blank cells in that column until the start of “Central Region” in row 200, followed by more blanks until “West Region” in row 500-you can quickly fill in the blanks using Fill Down. As shown in Figure 2, the Power Query Editor has a variety of tools on a ribbon, with tabs for Home, Transform, Add Column, and View. The first 1,000 rows of your data are shown in the Power Query Editor. Select one cell in your table (or the entire range if you’re using a named range) and select From Sheet on the Data tab. In the latest version of Microsoft 365, this command was recently renamed as From Sheet (see Figure 1). In older versions of Excel, choose From Table/Range. On the Data tab, look for the Get & Transform Data group. Power Query only works with either a table or a named range. Alternatively, you can use a named range for your data. To get started, format your data as a table either using Ctrl+T or going to Home, Format as Table. When you finish cleaning data in Power Query, the instructions are run in memory, and the output is a new worksheet with the cleaned data. The “changes” that you make using Power Query are saved as a set of instructions. The original data in the Excel worksheet remains unchanged. This month’s article highlights some of the extra features that are in the Power Query Editor but not easy to do in Excel.Īll transformations that happen in Power Query are nondestructive. Instead of “Text to Columns,” the equivalent command in the Power Query Editor is called “Split Column.” But if you open the Split Column drop-down in the Power Query Editor, you’ll see many extra options that are better than those in Excel.
When you first use the Power Query tools, it might feel like you’re in an alternate universe.
They were developed by a SQL Server team at Microsoft. The Power Query tools weren’t developed by the Excel team. If you do those steps with this month’s file and save the list of steps in a workbook, you can easily run those same steps next month.
How to get power query in excel 2016 mac download#
For example, you might download a report each month that requires a half dozen cleaning steps. Any cleansing steps that you perform with the Power Query Editor are saved as a procedure that can be run again the next time that you have similar data.
0 kommentar(er)
