Insert Data Input rows

Quite often we would like to insert a row in our table/matrix reports in Power BI and enter our own data. For example, a financial statement report connecting to a database query may be able to fetch revenue & expense metrics, but it might not retrieve the number of shares outstanding. Similarly, a sales report can leave out the sales data for a newly launched product category.

To address such scenarios, Lumel EPM allows you to insert static rows in matrix-style reports where you can input or enter the data.

1. Insert data input row

You can insert data input rows from the 'Home' tab > 'Insert Row' menu. Please bear in mind that the Insert Row button will be disabled until you select a row in your report. Select the Number option from the Data Input sub-menu.

Another way to insert static rows is by using the row gripper. The gripper icon is highlighted when you hover over a row. Click on it, then select the 'Insert' > 'Data Input' option.

1.1. Data input row properties

A side panel opens when creating a data input row. Type in the name of the row in the 'Title' textbox and click Create. You will see an empty record created above the row that was selected. You have successfully inserted a data input row. We will now discuss the properties of the Static Row panel below.

i. Row type

Row type dropdown helps you to create a calculated row, data input row, or aggregated row by selecting the appropriate option.

You have the flexibility to switch between data input, calculated, and aggregated rows at any time with the Row Type dropdown. For instance, you may have used a formula and referred to the values of an existing row dimension while creating the row. You can easily change it to data input and enter values without having to create a new data input row.

ii. Insert As

Select the Single Row option to insert one data input row.

Select the Templated option to replicate the row category across all levels of the hierarchy. For instance, you may create a product line specific to a particular region. You can use the Templated option to replicate the same product line across all regions.

The image below shows how template rows are rendered.

iii. Scaling factor

You can set the scaling factor for the static row depending on the data that is expected to be entered. It is set to Auto by default.

v. Include in total

When the 'Include in total' option is enabled, any values we type in for the new row also contribute to the parent row, in this case, 'International'.

iii. Distribute parent value to children

The 'Distribute parent value to children' option ensures that if a value is entered at a total level, it gets distributed to the levels below it.

iv. Bind for cross filter/RLS

You can enable the Bind for Cross filter/RLS option – this ensures that cross-filter selections and RLS settings apply to calculated/manual input rows that reference other rows. For example, if this option is not enabled, a manager handling Canada accounts can view a manually inserted row that references US data or an inserted region that is manually created at the visual level. Learn more about binding rows.

v. Default value

You can either enter a static value or select another row to source default values.

1.2. Delete a static row

To delete a static row, hover over the row and click on the row gripper. Select the 'Delete Row' option. You can also delete rows from the Manage Rows interface that will be covered in subsequent sections.

2. Bulk insert static rows

You can bulk-insert leaf-level rows or hierarchies using the Insert rows option.

2.1. Inserting child rows

To insert a leaf-level row, click on any child row or the parent row under which you want to create rows. From the row gripper, select the Insert Rows option. Notice that the parent category is already populated. You can key in the value for the child rows.

There are 2 ways to add new rows:

• To insert a new row, click the icon that appears on hovering near the parent category.

• Click Add New, then choose whether to insert a single row or bulk insert 5/10 rows.

The GAMING CONSOLE and CHARGERS rows are created when you click Save. You can enter values in these rows. The icon denotes manually inserted rows.

2.2. Inserting a hierarchy

To insert a new level in an existing hierarchy, go to 'Insert Rows' > 'Add New' either from the Row gripper or from the Home tab. Here, you can overwrite the default parent category that is auto-populated in the Insert Rows window. For instance, if you click on Pacific (or any child row), the Insert Rows will automatically have Pacific as the Region. Double-click and overwrite the value with the desired parent category.

2.3 Disabling row insertion

You can restrict users from creating new categories for a particular level. Navigate to Insert >Manage Rows > Row Settings> Insert Row Configuration.

In the image below, the creation of new regions is disabled. However, new subregions can still be inserted.

Notice how the Region textbox is disabled but we are able to enter values in the Sub Region and Category textbox in the image below.

If you have hierarchical row dimensions, when you disable row insertion for a specific dimension, this will automatically disable row insertion for all levels above it.

2.4 Uploading from an Excel sheet

You can upload the row categories from an Excel sheet. In the Insert Rows window, you will be able to see the data from your Excel as dropdown options. Let's see how.

STEP 1: Navigate to Home > Manage Rows > Insert Row Settings> Insert Row Configuration. Select Options list from CSV from the dropdown.

STEP 2: On selecting the option, The Add Options from CSV window opens. Click the Upload button. You can browse and upload the source file from the CSV Upload tab. Inforiver displays a preview of the contents of the CSV file.

STEP 3: You need to map the source field from the Excel, which will be used as row categories. Choose the field from the Excel from the Label Column dropdown. In this case, the mapping is as shown below:

STEP 4: Map the dimensions in the excel with the visual column names.

Once the steps discussed above are completed, you will be able to see the data from your Excel in the Region and Sub Region dropdowns.

Follow the steps discussed in sections 2.1/2.2 to insert the configured row categories into your visual.

2.5. Allow blank values in categories

While inserting row hierarchies manually, you can enable the Allow Blank Values toggle if you expect blank row categories in the leaf nodes. This option is available at Home tab > Manage Rows > Row Settings > Insert Row Configuration > Allow Blank Values.

By default, this toggle is disabled. The blank categories are highlighted in a red error box, and Lumel EPM prevents you from inserting rows.

By enabling this option, you can insert row hierarchies that contain blank leaf categories.

Please note that you cannot create blank parent nodes for child nodes that are not blank.

3. Row hierarchy

This option can be used to insert a single user-defined level for hierarchical data. Only one row is created while using this option, unlike the Insert Rows(s) option, where you can define the hierarchy structure with multiple nested levels and rows.

After creating a custom hierarchy level, you can insert static rows, calculated rows, aggregated rows, etc.

4. Merge rows

You may be expanding to new regions or adding new product lines - with the Static Row, Insert Rows(s), and Row Hierarchy options, you can add dimension categories on the fly. When the same category exists at the visual level and in your semantic model, you will be able to merge the rows from your dataset.

The icon that indicates a manual input row will be replaced bywhen a row dimension category is available in the source system. Click the icon to merge the row and then click Proceed in the Merge rows dialog box.

Notice how the merge icon disappears and the data input field becomes editable after merging the EURIBOR row. When you merge rows, static rows are replaced with native rows however data inputs are retained.

In the next section, we'll be looking at inserting manual input columns.

Resources

Insert row and manually enter data in Power BI matrix reports