Excel Import where the starting row changes

Question

I have a xlsx report format where the first x rows are parameters of the report, date ran, who ran it etc and then there is a blank row and then the headers and data start.

I can import this excel into Alteryx by setting it start the data on row 10 or wherever the header row starts. However, I'm trying to make the workflow more dynamic so if someone adds an extra filter, which would push the data down, or removes a filter, so it would pull the data up, Alteryx would just automatically adjust without me needing to modify the "start import on row" line. Also, the number of columns may change and the column header names also may change.

The way our workflows are set up i cannot modify the workflow every time to run it and hence looking for an macro that can be applied to existing workflows with this sort of excel files.

Wondering if there is a way without using an Analytical app to pick the right starting row

Sample data.xlsx

flying008 · Answer

Hi, @Julia_Mathew

The exhaustive method may be foolish but effective.

[F1] NOT IN ("Report Parameters","Report Name","Layout Name","Report Submitted By","Report Run Start Date","Report Submission ID","Report Parameters","Event Created Date","Number of Rows","Report Run End Date")

ScottLewis · Answer

Try this version. This is what I posted before but modified so that instead of looking for a specific value it is looking for the second block of data. Should work whether or not there are blank rows above the report criteria.

MovingDataRange.yxmd

Julia_Mathew · Answer

No that is the issue - book name is not always going to be the column header or even the first column but there will always be a blank row after the report parameters and before the header row starts.