Hi @DiKi-I , Thank you for reaching out to the Microsoft Community Forum.
You're currently stuck manually entering row numbers in Dataflow to extract metrics like net revenue and profit from a sheet that only contains numbers, with no metric names to filter on. Since there's no label or identifier in the data itself, dynamic filtering is tricky but there are a few ways to handle this. The most reliable method is to maintain a separate mapping table that defines the start and end rows for each metric.
For example:
| Metric | Start Row | End Row |
| NetRevenue | 20 | 200 |
| Profit | 201 | 300 |
You can reference this table in your Dataflow logic to dynamically extract the rows you need, which avoids hardcoding and makes updates much easier when the structure changes. If you have control over the data before it reaches the sheet, another option is to tag each row during ingestion with the metric name in a separate column.
For example:
| Metric | Value |
| NetRevenue | 100.2 |
| NetRevenue | 105.6 |
| Profit | 55.1 |
This lets you filter rows dynamically by metric name in Dataflow without relying on row positions. Finally, if your sheet has consistent patterns like blank rows between metric blocks, you can use a Google Apps Script to detect the start and end of each block automatically.
Example:
function detectMetricBlocks() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const blocks = [];
let start = null;
for (let i = 0; i < data.length; i++) {
const isEmpty = data[i].every(cell => cell === "" || cell === null);
if (!isEmpty && start === null) start = i + 1;
else if (isEmpty && start !== null) {
blocks.push({ start, end: i });
start = null;
}
}
if (start !== null) blocks.push({ start, end: data.length });
return blocks;
}
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
