The Analytics Edge Standard Add-in allows you to make several transformations to your data before you write it into your worksheet. Once your query has been created, a Functions Wizard pops up offering you the ability to Pivot, Arrange, Sort or Filter your data as well as options for when you Write To Worksheet.
When editing a query, any tabs that have non-default settings will be *highlighted* with asterisks so you know they contain special selections. If you change the query itself, some of these selections may need to be updated (such as selected columns or filter values).
Columns to Keep – select and rearrange the columns you want to keep in the resulting table. Select ‘– No Pivot‘ to leave the data as-is.
Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column.
Cell Values – select a column to be used for the values in the cells in the pivoted table.
Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.
No Change – do not change the order of the columns.
Arrange By Name – Use this option to rearrange the columns based on the names of the columns. This is useful when you get imported data that may have additional, missing, or out-of-order columns.
Arrange Selected Columns – Use this option tab to arrange the columns by the names of the columns themselves. Select the columns you want arranged, then the sorting options. Note: the columns are identified by the column letters, not the actual names themselves. This allows you to sort the results of the Pivot operation, which could result in changing column names.
Create one or more sorting rules which will be applied in sequence to order your data. If two rows are equal in value for the first rule, the next rule is used to determine the order. You can specify as many rules as required.
Add Sorting Rule – select a Column and sort order (Ascending/Descending), then click Add to add it to the Sorting Rules.
By Name – You can select columns by name or by position (default). This allows you to sort by columns with names that may change as a result of a Pivot operation.
Sorting Rules – you can change the order of the sorting rules, or remove any of the rules using the arrows and X buttons.
Add Filter Rule – select a Column and filter criteria, enter a value, then click Add to add it to the Filter Rules. Filters will be combined using AND logic (rows must pass all filters), and you can choose to Keep or Remove the matching rows. While typically used to remove unwanted data from a report, it can also be used to selectively keep rows of interest.
By Name – You can select columns by name or by position (default). This allows you to filter by columns with names that may change as a result of a Pivot operation.
Filter Rules – you can change the order of the filter rules, or remove any of the rules using the arrows and X buttons.
Write to Worksheet Function
You can choose to Write to Worksheet (replace the previous query results) or Append to Worksheet (added to the existing query results in the worksheet).
Do not include header – check this to prevent the column names from being included in the first row of the results written.
Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data (numeric versus text versus date).
Keep both – When appending to the existing data in the worksheet, simply append all the new data to the existing data. This may results in duplicate rows.
Do not change existing rows – If the new data contains rows with matching text and date columns, only append rows with new combinations. This does not change the previous reported values.
Update to new values – If the new data contains rows with matching text and date columns, replace the existing numeric values with the new values. This will refresh the previously captured values.