- Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.
In this post, you will learn how to shape data in Power Query through a broad array of sources. These range from editing queries, formatting tables, adding columns, hiding rows or columns, and splitting a text column.
Note: Power Query is also known as Get & Transform in Excel 2016. Information given here relates to both. For more details about this, see Get & Transform in Excel 2016.
Moreover, using Power Query equips you with the ability to shape data from several sources. This is done through modifying the query steps to reflect your data analysis specifications.
Resources to learn about shaping data
The resources below will give you a greater insight into how to use Power Query more effectively, depending on your specific needs for data analysis.
- Firstly, with the Query Editor, you can sift through, define, and conduct data-transform operations across a data source.
- Additionally, Microsoft Power Query for Excel enables you to import data into Excel from a multiple range of data sources. You can also use the Query Editor to shape data by amending query steps.
- Shape data from numerous data sources by adding, erasing or changing query steps. When doing this, it is crucial that they correspond to your data analysis requirements.
Refresh a query to transfer the newest data into a table without generating the query once more.
Extract data from various data sources and integrate it.
- Filter a table to shrink the size of query results by omitting rows or columns according to on size, value, or condition.
- Sort table rows in your query results ranked by a criteria. For example, like the alphabetical or numerical value of one or several columns. Although, it could also be executed by ascending or descending order.
- Group the values from different rows into a single value. More importantly, this will be dependent on the values in one or more columns.
- Expand a column with an associated table to present the related data, then extract some or all column values from that table.
- Aggregate data from any column including an associated table to showcase the results of a group operation. This could comprise Sum, Count, Average, Min, and Max.
- Add an Index or Custom column to your current query.
- With the Steps pane, add, modify, rearrange, or remove query steps to adjust how data is transformed.
- Combine numerous queries, by merging or appending them. The Merge and Append operations are undertaken on any query with a tabular shape. This happens with no consideration of the data’s source.
- Merge values in two or more columns in a query.
- Delete selected columns or Remove Other Columns from a query.
- Erase rows from a query that displays data errors.
- Upgrade a row to be a column-heading row.
- Divide a column of text into a series of columns. This can be done either by delimiter or by a number of characters.
- Append data from a query into an Excel worksheet. Once you add data from a query, you can choose to load a query to the Excel Data Model.