Once you have connected Excel to the platform via OData, Power Query opens with a preview of your data. This article covers how to shape that data effectively, configure Power Query settings correctly, and avoid common performance problems.

​

The Query Editor has four main tabs. Home covers saving, refreshing, removing columns, filtering, and sorting. Transform handles data type changes and text, number, and date transformations. Add Column lets you create calculated columns or indexes. View adjusts the editor display and opens the Advanced Editor.

The Applied Steps panel on the right records every action as a step. You can click any step to review it, edit it, or remove it. This makes your data preparation repeatable and easy to debug.

​

Select one or more column headers and click Remove Columns, or use Choose Columns to select exactly which columns to keep. Removing unused columns reduces payload size and speeds up refreshes.

​

Click the dropdown arrow in any column header to sort or filter. For example, filter a Status column to show only active records. Apply filters as early as possible in your step sequence — filtering after a large data pull is significantly slower than filtering before it.

​

Select a column and choose the correct type from the data type dropdown. Common needs include converting a date-time field to date only, or rounding numeric values.

​

Select the column to group by, click Group By, and choose an aggregation (Sum, Count, Average, etc.). For example, group transaction data by month and sum the value for each month.

​

When your data is ready, click Close & Load. Excel retrieves the full dataset and creates a table based on your transformations. To refresh with the latest data, use the Refresh button in the Queries pane or on the Data tab. Your transformation steps are preserved and re-applied automatically.

​

The way you structure Power Query requests has a significant impact on performance. Poorly written queries can retrieve far more data than necessary.

Always start with the service document. Begin your connection with the base OData URL, then select the object in Power Query. Avoid building a URL that already includes the object name — this can cause Power Query to retrieve the full dataset before applying any filters, sometimes more than once.

Filter early. Apply filters as soon as possible in your step sequence. Server-side filtering — where filters are pushed down to the OData service rather than applied inside Excel — dramatically reduces data transfer. Filtering by date range or status before any other transformations is almost always the right approach.

Select only the columns you need. Use Table.SelectColumns or the Choose Columns option to limit the response to required fields. Do not retrieve columns you do not use.

Avoid merging queries where possible. Merging queries can trigger multiple full data pulls. Keep queries self-contained.

Be cautious with expand steps. Expand operations can be expensive. Filter first, expand last.

Use Table.Buffer sparingly. This stores intermediate results in memory and can prevent repeated server requests during complex transformations. Only use it when you are applying multiple transformations to a filtered dataset and want to ensure the smaller dataset is reused in memory — not as a default step.

​

Access settings in Excel at File \> Options and Settings \> Query Options. Global settings apply to all Power Query work on your machine; Current Workbook settings apply only to the active file. Be careful with Global changes.

Background data. The "Allow data preview to download in the background" option speeds up switching between queries but uses more memory and CPU. For large queries or many data sources, disabling it may improve stability.

Cache. Power Query maintains a persistent disk cache of behind-the-scenes requests. If something is not behaving as expected — stale previews, unexpected results — clear the cache at Global \> Data Load \> Clear Cache. Each query refresh has its own cache scope, so clearing it forces a clean reload from the data source.

Privacy levels. Privacy settings control how data sources can be combined. Set the Privacy Level to Organisational for platform data connections. This allows the data to be combined with other sources while remaining restricted to your organisation. Some users set privacy to "Always ignore Privacy Level settings" during development to reduce combination errors, but review this carefully if you are handling sensitive data.

Regional settings. Ensure your regional settings match your data format, particularly for dates. A date written as 1/10/2024 means different things in different regions, and incorrect regional settings can produce subtle, hard-to-spot data errors.

​

A reliable pattern for reporting workbooks is to store connection details and report parameters in named Excel tables, then reference those values inside Power Query rather than hard-coding them. This lets users change the server, date range, or filter values without opening the query editor.

A well-structured query sequence typically looks like: retrieve connection details from a parameters table, connect to the base OData URL, select the object, select required columns, apply filters, then sort or transform as needed.

If performance problems persist, review each Applied Step and watch how row counts change after each transformation. Check whether filtering is happening before or after large data is retrieved.