Azure Data Flow splitting dataset into multiple tables

Question

Hi team. I'm utilizing Azure Data Flow to read in an excel file with multiple tabs of data. One of the tabs contains three sets of data with the same schema, that is required to be broken out into separate datasets. The data is formatted similar to this:

We're not able to define ranges for each set of data because the # of rows changes. Because each set of data shares the same column headers, I'm thinking that a test needs to be performed to identify the header and then write the succeeding rows to a dataset until the next header or when the next row is null (after last dataset).

Any suggestions for implementing this logic would be appreciated.

Ty.

Hans Kiamzon

Answer 1

You can do this with a combination of keyGenerate (Surrogate key) followed by(branch/filter then join) and then Split.

You will branch and filter all header rows with their row position. You will then use a Agg tx with empty groupBy and use collect function to get all the header row indices as one row.

Join the header row indices(single row) with the full data as cross join.

Then use the information in the joined data to split into 3 streams.