You can connect to spreadsheet data that's stored in local files by uploading the files in PowerMetrics and creating data feeds. Then, using those data feeds, you can visualize your data in custom metrics.
This article includes:
- Important notes
- Creating a data feed using an uploaded file
- Manually updating data feeds that use local files
- Next steps - Creating custom metrics
Here are a few things to note before creating your data feed:
- Supported file types and size: We support Excel, CSV, JSON, and XML file formats and a 10 MB maximum file size.
- Number of columns: If your source data includes more than 100 columns, only the first 100 will be included in the data feed. We recommend using smaller data sets for your data feeds and metrics. If you need to create a data feed that includes 100 columns, please contact our Support Team to discuss increasing your limit.
- Modifying your spreadsheet data in PowerMetrics doesn’t change your original spreadsheet source data.
- The importance of column header names: PowerMetrics uses the column header names in your spreadsheet source data as unique identifiers. This lets us map to the columns in your source data, enabling your data feed and custom metrics to continue to point to the correct column, even if the content and order of the columns in the source data changes. For example, with this feature enabled, if a column is removed from the raw source data that a custom metric is using for its data, then the metric will stop updating. However, if you add the column back to the raw source data (using the original column header name), the metric will start working again and retroactively ingest all of the data that’s in the raw source data column.
To enable this feature, when creating your data feed and selecting the row to use as the “row with column headers”, you must choose a numbered row – not the “None. Use letters (A, B, C, D)” option.
To ensure your data feed (and custom metrics) continue to update correctly over time, after creating the data feed, don’t change any names in the column header row in your source data. This action would break the mapping to those columns.
Column header row names are case sensitive. For example, if you change a name from “Revenue” to “revenue” in your source data, PowerMetrics will identify “revenue” as a new column and custom metrics that were pointing to “Revenue” will break.
Each column header name should be unique. If your source spreadsheet data includes duplicated column header names, we recommend you modify it and replace duplicated names with unique ones before using it to create a data feed. If not, we’ll automatically assign new, unique names to duplicate column headers in the data feed editor. For example, if your source spreadsheet has 3 columns named “Profit”, they’ll be updated in the data feed to “Profit”, “Profit 1”, and “Profit 2”. If desired, you can rename these automatically-named columns in the data feed editor.
Creating a data feed using an uploaded file
To create a data feed using an uploaded file:
- Add a new data feed by clicking the + button beside Data Feeds in the left navigation bar. (See below.)
- Click Select data.
- On the Where is your data? page, under Core Data Services, click File upload. (See below.)
Note: The same instructions apply if you click File Upload Excel, File Upload Json, or File Upload XML.
- On the Configure data service page, select the file to upload either by dragging and dropping it here or clicking Choose file.
- The name of the chosen file displays under File. If you want to select a different file, click Change file.
- For multi-sheet files, under Sheet name, select the sheet you want to work with. The chosen sheet will display in the data preview.
To create a data feed that includes data from more than one sheet, create single data feeds and then merge them. Learn how here.
- To work with metrics, the measures and dimensions in your data feed need to be organized into columns, not rows. If applicable, under Settings, at Change rows to columns use the toggle to switch row-oriented data to column-oriented data.
Note: This setting does not apply to files with JSON or XML formats.
- At Row with column headers, we do our best to auto-select the correct row to use as your column header row, but, if desired, you can use the drop-down to manually select the column header row instead.
If your columns don’t have header names, select None to use letters, beginning with “A”, to refer to your columns.
Notes: All rows before the selected column header row will be excluded from the data feed. This setting does not apply to files with JSON or XML formats.
- When you’re done configuring settings, click Use this data.
- You're taken to the data feed editor, where you can make changes to the data feed or save it as-is.
Note that the data feed is automatically named to match the source file. Typical editing actions here include changing the data feed’s name and entering a description.
If you want to make more extensive changes to the data feed, go here for detailed information.
- Click Save data feed.
If, before saving the data feed, you want to return to the Configure data service page, click in the field under Data service. (See below.)
The data feed is added to your account and is ready to use for custom metrics. To see a list of all your data feeds, click Data Feeds in the left navigation sidebar.
Manually updating data feeds that use local files
If you modify the local file and want to update the data feed (and its associated custom metrics) to reflect your changes, you'll need to overwrite the version you previously uploaded.
Note: When you make changes to data that's stored on the cloud (for example, data in Google Drive) the connected data feeds are refreshed automatically.
To manually update a data feed:
- Click Data Feeds in the left navigation sidebar to open your list of data feeds.
- Select the data feed you want to modify.
- Click the Edit button in the top navigation bar.
- To update the data feed, click in the field under Data service. (See below.)
- On the Configure data service page, click Change file.
- Select the most recent version of the file and upload it by dragging and dropping it here or by clicking Choose file.
- Click Apply.
- Click Save data feed in the data feed editor.
The data feed and its associated metrics are updated.
Next steps - Creating custom metrics
Now that you've created one or more data feeds that connect to data from uploaded files, you can return to them and use them to make custom metrics. A single data feed can be used to create a single or multiple custom metrics.
To create custom metrics using a data feed:
- In the left navigation sidebar, click Data Feeds to open your list of data feeds.
- Select the data feed you want to use for your custom metric.
- Click either +Add metric button in the top right corner of the window or open the Metrics tab and click +Add metric. (See below.)
- Choose settings for your custom metric and click Save metric. If you need help, go here to learn more.
The metric is added to your list of metrics, accessed by clicking Metrics in the left navigation sidebar.
View your metrics from multiple perspectives. Explore and analyze your data. Gather your metrics onto dashboards and share them with your colleagues. Track your progress as your data grows and changes over time. Most importantly - use the insights you get from PowerMetrics to make the right decisions at the right time.