Klips and PowerMetrics: Preparing spreadsheets for data sources

 

We've recently redesigned the data transfer experience for PowerMetrics customers by combining raw and modelled data sources in a single object called a data feed.
If you're using PowerMetrics and see Data Feeds in the left navigation sidebar, go to this article instead.
The data feed feature is being released gradually. If you don't see data feeds in your account yet - no worries - they’re coming soon!

 

Klipfolio works best with data that is properly and consistently formatted. It’s faster and easier to build visualizations using properly formatted data.

We recommend you follow these best practices when preparing your data for upload either locally or through a file sharing service.

Data format

  • Ensure your numerical data is in a standard decimal format. For example 1000000.29.
  • If possible, remove any non-numeric characters from numerical data. For example, currency ($) symbols.
  • Ensure all numeric data columns are set as a numeric data type.
  • Ensure all non-numeric data columns are set as a text data type.
  • Ensure that the spelling and spacing of the values in dimension columns are consistent to prevent unwanted unique values.
  • If you're using PowerMetrics, you can benefit from its pre-built date filters by including day, month and year in all of your date references. For example, 30-07-2019.

Data structure

  • To ensure accurate data, remove all external references, such as vlookup and pivot tables.
  • Ensure that date columns have a consistent 2-digit date format. For example, DD-MM-YYYY.
  • When including columns that add dimension to your data, ensure that they are listed in their own columns. For example, if your data is segmented by country, all countries should be in the same column.
  • If possible,
    • have your data in columns rather than rows. This is especially important for date values. Having your data in columns ensures that new data will get added to your visualization as soon as the data source is refreshed.
    • remove total rows.
    • ensure each column has the same number of rows.
    • fill blank cells with text, such as N/A or NULL.

File format

  • If you're using Klips, remove any trailing spaces from Excel worksheet names. Otherwise you will not be able to select your data within the Klip Editor.
  • You can have multiple sheets in your file. When modelling a data source or when using the Klip Editor, you can select the sheet you want data from using the Sheet drop-down menu.
  • To avoid slow performance, due to limits imposed by your web browser, the maximum file size for any data source type is 10 MB. Learn more about file sizes and how to decrease your data source size here.
  • Modelling your data source cleans up your data. You should keep both the underlying (raw) data source and the modelled data source in your account. The underlying data source is the data source that refreshes the data for your modelled data source.

Related articles

Have more questions? Submit a request