How to Shape Data (Power Query)?

A QR barcode being assembled - symbolises editing query step settings.

  • 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.

Getting Started with learning how to shape data (Power Query).
© Microsoft, 2020.

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.

Introduction to Microsoft Power Query for Excel

  • Firstly, with the Query Editor, you can sift through, define, and conduct data-transform operations across a data source.

How-to : Import data from external data sources

  • 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 or transform a query

  • 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

Refresh a query to transfer the newest data into a table without generating the query once more.

Combine data from multiple data sources

Extract data from various data sources and integrate it.

Filter a table

  • Filter a table to shrink the size of query results by omitting rows or columns according to on size, value, or condition.

Sort a table

  • 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 rows in a table

  • 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 containing an associated table

  • 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 a column

  • 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.

Insert a custom column into a table

  • Add an Index or Custom column to your current query.

Edit query step settings

  • With the Steps pane, add, modify, rearrange, or remove query steps to adjust how data is transformed.

Combine multiple queries

  • 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 columns

  • Merge values in two or more columns in a query.

Remove columns

  • Delete selected columns or Remove Other Columns from a query.

Remove rows with errors

  • Erase rows from a query that displays data errors.

Promote a row to column headers

  • Upgrade a row to be a column-heading row.

Split a column of text

  • Divide a column of text into a series of columns. This can be done either by delimiter or by a number of characters.

Insert a query to the worksheet

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: