The following transforms might make original row information invalid or otherwise unavailable. In these cases, the function returns null values:

• pivot
• flatten

• join
• lookup
• union
• unnest
• unpivot

Derive Example:

Output: Generates a new OriginalRowNums column containing the row numbers for each row as it appeared in the original data.

Sort Example:

Output: Rows in the dataset are re-sorted according to the original order in the dataset.

Output: Deletes the rows in the dataset that were after row #101 in the original source data.

There are no arguments for this function.

Example - Header from row that is not the first one

Example - Using sourcerownumber to create unique row identifiers

The following example demonstrates how to unpack nested data. As part of this example, the SOURCEROWNUMBER function is used as part of a method to create unique row identifiers.

Example - Delete rows based on source row numbers

Source:

Your dataset is the following set of orders.

Transform:

Initially, you want to review your list of orders by last name.

During your review, you notice that two customer orders are no longer valid and need to be removed. They are:

• LastName: Hall
• LastName: Jones

You might hover over the left side of the screen to reveal the row numbers. You select the row numbers for each of these rows, and a delete suggestion is provided for you. When you click Modify, you see the following transform:

The above checks the results of the SOURCEROWNUMBER function, which returns the original row order for the selected rows. If a selected row matches values in the [2,7] array of row numbers, then the row is deleted.

Results:

When the preceding transform is added, your dataset looks like the following, and your sort order is maintained:

Source: