How do I model a data source?

Our recent redesign simplifies the data transfer experience by combining raw and modelled data sources into a single object - data feeds.
If you see Data Feeds in the left navigation sidebar, go to this article.
These new features are being released gradually. If you don't see them in your account yet - no worries - they’re coming soon!

This article includes:

Where to begin
Setting overall properties for a modelled data source
Setting column properties – name and data format type
Writing formulas for a modelled data source
Editing a modelled data source
Building custom metrics and Klips with modelled data sources
A few general notes about modelled data sources

Where do I begin?

The following procedure describes how to enter the modeller using an existing raw data source. PowerMetrics requires modelled data sources. As such, you’ll automatically be taken to the modeller when you create a custom metric or a data source in PowerMetrics.

To select a raw data source to be modelled:

Click Data Sources in the left navigation bar.

If you don’t see the above option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.

From your list of data sources, select the one you want to model.
Click Create modelled data source.

Note that you can also model your data as you create a raw data source by selecting the "Model your data" checkbox in the "Configure your data source" page.

Setting overall properties for a modelled data source

The Properties panel, that displays on the right, is used to select general settings for your modelled data source, including the following:

Name: Type a name for your modelled data source here. You can also hover and click on the title at the top-left of your page to name your modelled data source. The name you choose will display for your modelled data in the Klip Editor and in your metric and help you identify your modelled data source in your list of data sources.
Description: Type a description for your modelled data source here to identify its purpose.
Column Names: Under Use text in row, click the drop-down list and choose the row number that contains your column headings. Note: This option is toggled off by default when creating custom metrics and data sources using the query builder, as it’s not applicable.
Exclude Rows: Enable Exclude data before row to choose the row number you want your data to start with in the modelled data source. For example, if you choose 5, the data in your modelled data source will start with row 5 and onward. Refer to the digits that display on the left side of the rows to help you select the right number of rows to exclude. Note: This option is toggled off by default when creating custom metrics and data sources using the query builder, as it’s not applicable.

You can add individual columns to your modelled data source using the + button (which displays beside the last column in your data) or via the drop-down arrow button for each column, or via the toolbar at the top of the modelled data source window. After adding a new column, you can populate it with data by clicking a column in your unmodelled data source (that displays underneath your modelled data source window). If you are using tree-structured data you must add each column of your modelled data source individually.

For more options, click the drop-down arrow button on the right-side of a column name. (See below.)

Access the toolbar at the top of the screen to add, duplicate, move, and remove columns. Use the arrow icons to undo and re-do actions. From the toolbar, you can also unpivot columns, merge data from other modelled data sources, add all columns from the unmodelled data source, remove all columns from the modelled data source, and open the Properties panel.

Setting column properties – name and data format type

The Column Properties panel displays on the right-side of the screen when an individual column is selected.

Note: When automatically setting the format, we do our best to select the correct format type. However, we recommend you take a moment to verify all formats are set as you expected.

Set properties and align your data by choosing a Name and a Data Format for each column.

Name: By default, column names are auto-set to match those from the original data source or data query. If you want to use a custom name for your column(s) instead, toggle the Automatically set name option to “off”. This enables you to enter your own custom column names. You can either enter a new column name in the Properties panel or in the column header itself.
Data Format: You can either let us automatically set the data format type for you or you can manually select one of the following data types for each column: Text, Number, Percentage, Currency, Date, or Duration.
Text: Textual data is indicated by ABC in the column header.
Number: Numeric data is indicated by 123 in the column header.
Percentage: Data formatted as a percent is indicated by a percentage symbol, %, in the column header.

When choosing this data format, use the formula bar to convert percentage values to decimals between 0 and 1, if they are not already. For example, if your values are 23, 33, and 55, add “/100” to the formula in the formula bar so that they become 0.23, 0.33, and 0.55.

Currency: Data formatted as currency is indicated by a currency symbol, $, in the column header.
Date: Date data is indicated by DATE in the column header.

Depending on the data service, you may need to set the Input Format for each column to match the format used in the corresponding column in the raw data source. (To display your raw data source, click the drop-down arrow in the column and select Edit formula). We do our best to auto-fill the Input Format for you but it’s a good idea to double-check that it aligns.

You can either use the default time zone for your account or select a different time zone from the drop-down list. Note: This setting is not applicable if your data source doesn't include hourly data.

For each column, optionally, select a Display Format that differs from your Input Format. Your choice is reflected in the selected data column and will be used in your modelled data source.

Duration: Duration data is indicated by DUR in the column header.

Duration values are consumed in seconds. The Input Format is used to convert the duration values to their corresponding value in seconds. You must set the Input Format for each column to match the format used in the corresponding column in the raw, unmodelled data source. (To display your raw data source, click the drop-down arrow in a column and select Edit formula).

The display format for duration is “Days:hours:minutes:seconds”, depending on your data. For example, if the duration values in the column are all less than one day, then the display format will be “hours:minutes:seconds”.

Note: To select from our pre-set input format options, the duration units in your raw, unmodelled data source must be either colon-separated units or single units. For example, “dd:hh:mm:ss”, “d:h:m:s”, and “1234” (where the input format is minutes). You can, alternatively, select the Custom option.

Writing formulas for a modelled data source

In the formula bar you build formulas, using functions and references, to manipulate, refine, and perform calculations on your data. For example, you can combine related information from separate columns into a single column or group your data to hide duplicate values. The possibilities are endless.

To open the formula bar:

Right-click any column, or click the drop-down arrow button in any column, and select Edit formula. You can also open the formula bar by double-clicking any column. When you open the formula bar, your raw, unmodelled data source opens below.

To close the formula bar:

Click the drop-down arrow button in any column and select Hide formula or click anywhere in the dark grey workspace surrounding your modelled data source. When you hide the formula bar, your raw, unmodelled data source is also hidden.

Notes on writing formulas in the modeller:

Variables and formula references are not currently supported when creating or editing a modelled data source.
In the modeller, when building formulas using results references, we recommend using consistent reference types within the formula, for example, results references (&) should be combined with results references, and datasource references (@) should be combined with datasource references. Otherwise, you may notice misalignment in your results. This is because datasource references include all rows of your raw data source (including headers and leading blank rows), while result references are the final result of your modelled column (with the headers and excluded rows removed). If you want to combine datasource and results references, we suggest you create a modelled column that points to your raw data source column and include a results reference to this column in your formula.
Tip: Select a column in your data and click the Evaluate button. Using this method you can compare information for a column in the raw data source to the same column in the modelled data source to see if the rows align. Note that the number of rows that displays for a modelled data source column may be misleading because, when modelling, we automatically add blank rows to columns when necessary to ensure row alignment.

For more detailed information on writing formulas for a modelled data source:

Editing a modelled data source

When you are finished creating your modelled data source, make sure to Save and Exit. You can always go back and edit your modelled data source later.

To edit a modelled data source:

Click Data Sources in the left navigation bar.

If you don’t see the above option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.

From your list of data sources, select the modelled data source you want to edit.
Click Edit Model or Edit Feed.

Note: If the raw data source that your modelled data source is based on changes, you may get an error asking you to confirm that the changes are reflected correctly when viewing the modelled data source. Once you have made the necessary changes, click Save and Exit to dismiss the warnings. Go here for more information on error resolution.

Building custom metrics and Klips with modelled data sources

Once you've created a modelled data source, you can use it to build custom metrics and Klips. Navigate to the About this Modelled data source page by selecting a modelled data source from your list of data sources, then click Create a metric or Create a Klip.

Some general notes on modelled data sources

Here are a few things to note about modelled data sources:

Modelling dynamic data sources is not currently supported.
When modelling a data source that has more than 30 columns, only the first 30 will be automatically added. Learn more here.
When viewing a modelled data source containing over 100 rows of data, all rows may not display at once. To access options to show more rows and to go to the last few rows or first few rows. (See below.)

If you have a multi-sheet data source, for example an Excel file that has data in multiple sheets, you can access the data in the other sheets when modelling your data source. After bringing in your multi-sheet data source, double-click a column to select it. You can select the sheet you want to add data from by using the Sheet drop-down menu on the right side. You can use this drop-down to switch between sheets when writing formulas and creating models. (See below.)

Learn more

Want to learn more? Check out these additional Knowledge Base articles:

You can also watch this video on modelling your data:

How can we help?