One of the best features of Data Studio is the ability to easily explore large datasets containing millions of records and hundreds of columns of data.
There are a number of ways to explore your data in a grid:
Once in Explore mode it is possible to apply actions to the data. These actions are mostly the same as their Workflow step counterparts.
Filter your data by selecting a column and entering a comparison value to match against using an operation like Equals, Contains or In Range. To filter on multiple columns and/or multiple comparison values create an advanced Function that returns a boolean (true or false) value.
In Explore mode, right-click on a value in a column and select one of the options Keep or Remove to show or exclude all rows containing the chosen value.
The Split Workflow step is the same as Filter but has the benefit of two outputs to see the rows that pass the filter and the rows that fail the filter.
Limit the number of rows or take a random sample or rows.
Examples for consecutive rows:
Examples for non-consecutive rows:
In Explore mode, the Columns action is used to update the order in which columns appear and to exclude/hide (or show) columns from the output. Note this is done using the Transform step within a Workflow.
Right-click on a column header and select Exclude.
Sort your data in ascending/descending order.
Move columns from 'unsorted columns' to 'sorted columns', set the sort order (min to max or max to min) for each column using Flip sort direction or the up/down arrow on the right of each 'sorted column'.
Select a 'sorted column' and use the 'move up'/'move down' buttons to change the priority order if sorting on mutliple columns.
Grouping will reduce the number of rows in a table.
The default output of a Group step is the Count aggregate, which returns the total count of rows. This count will then be broken down by each unique value in the group column.
Moving columns from ungrouped to grouped will show the distinct/deduplicated values in the column (along with the optional Count of the number of times each combination of values appears in the data).
The Add aggregate button is used to create a new column(s) containing a value or metric for each group.
Give the new aggregate column a name or optional suffix value if applying an aggregate on Multiple columns, then select the type of aggregate to use.
| Aggregate | Description |
|---|---|
| Count | Returns a count of the number of items in each group. |
| Count excluding nulls | Returns a count of the number of items in each group ignoring null values. |
| First value | Returns the first value in the column for each group. |
| First populated value | Returns the first value in the column for each group that is not null. |
| Last value | Returns the last value (based on input row order) in the column for each group. |
| Minimum value | Returns the minimum value in the column for each group. |
| Maximum value | Returns the maximum value in the column for each group. |
| Grouped values | Returns a comma separated list of all values in each group. The order is as they appear in the input. |
| Sorted grouped values | Returns a comma separated list of all values in each group sorted A to Z. |
| Reverse grouped values | Returns a comma separated list of all values in each group sorted Z to A. |
| Average | Returns the average (mean) of the values in each group. |
| Median | Returns the median (based on a sort of the values) of the values in each group. |
| Sum | Returns the sum of the values in each group. |
| Standard deviation | Returns the standard deviation of the values in each group. |
| Standard deviation (population) | Returns the standard deviation of the values in each group where it is known that the values represent the whole population. |
| Variance | Returns the statistical variance of the values in each group. |
| Variance (population) | Returns the statistical variance of the values in each group where it is known that the values represent the whole population. |
| Group ID | Returns a numeric value that can be used to identify each group. |
Non-numeric values will be excluded (ignored) from numeric aggregation calculations. Zero will be returned by default if the values are all non-numeric.
In Explore mode you can drill into a particular group to see the rows that belong to it by right-clicking and selecting View rows in group.
This ability to drill down into grouped values will be retained if the step is connected to any of the following subsequent steps: Branch, Chart, Export, Filter, Fire event, Output, Sample, Sort, Split, Take Snapshot, Transform and Union.
Manipulate and configure your data.
This step allows you to add/remove various Functions and arrange or columns.
Profiling helps you quickly understand your data, showing the uniqueness, completeness, frequency, outliers and more. Find out more about profiling data.
In data where values are repeated, i.e. the same customerId could appear many times in sales order data, this tool can be used to both check that related columns always return the same value and highlight where there are conflicts.
Learn more about identifying data conflicts.
Visualize you data. Learn more about Charts.
Any actions applied to the data are listed in the order they were applied in Actions list on the right-side. Clicking on an action will show the data as it appeared before additional actions were applied. Right-click (or menu) on an action shows options to Configure, Rename, Delete or Revert to (delete all actions applied after the selected action).
It is possible to minimize this panel to show more of the grid using the right arrow at the bottom of the panel.
The ability to easily drill down into the underlying rows behind your results in known as interactivity or interactive drilldown. Data that has been grouped, profiled, validated or analyzed for conflicts will show additional actions buttons like Values, Formats, Show passing rows, View rows in group.
For example, exploring data grouped with a count by 'Country', a user can right-cick on 'France' to see which specific rows contributed to that count. Similarly show the rows that have failed the user-defined rule 'Must be a valid date'.
Right-clicking on grid cells or column headers shows options to quickly apply an action such as a filter, sort or an interactive drilldown.
A count of both the columns and rows is shown in the bottom right corner of the grid. Clicking on these values allows you to search for and quickly jump to a particular column or row.
A user with the correct permissions can save the actions they have applied using the button Save as View (or Save as Chart). The original Dataset remains unaltered. Learn more about Views.
When editing a Workflow, clicking Show step results on any step opens the grid in Explore mode allowing you to check the results are as expected. Applying any actions like Filter, Group, Transform, etc. will enable the Add to Workflow button that will return you to the Workflow with the actions converted into corresponding Workflow steps.
