Split/extract multiple tables from a spreadsheet

[B_atWork]

I have a spreadsheet that contains scores of tables of survey data analysis. I'd like to find a way to split them out into multiple tables in my data model. I've decribed the format below and added a link to a sample. I've read about the Table.Split function, but there are no set number of rows that I can use for the pageSize. I was thinking if I can calculated the pageSize number by counting the number of rows between two strings I could pull this off, but it seems like Table.Split doesn't work like that. Is there something else I can do, or should I just split out these tables manually? Thanks in advance.

Link to file

The format goes like this:

Row 1 is blank

A header in row 2 that reads "Custom Table"

A blank row

A row with a subheader that reads "Notes" followed by a table with data that I don't care about

2 blank rows

A row with a path in the first cell

2 blank rows

A header in the next row that reads "Table 1"

A blank row

A table with data that I care about in the form of a matrix: question responses in the rows, categories in the columns, and numbers in the cells

Another blank row after the table

Another subheader followed by a table with data that I don't care about.

1-3 rows of notes

2 blank rows

Then the process repeats

[HotChilli · Accepted solution]

Did you get the "suggested tables" feature on the Navigator screen (bottom left) when you connect to the Excel spreadsheet?

[HotChilli]

The automated code generated is quite impressive.

Also, there's another technique where you go into Excel and define a range on each table and powerbi can recognise that.

Good luck

[HotChilli · Accepted solution]

Did you get the "suggested tables" feature on the Navigator screen (bottom left) when you connect to the Excel spreadsheet?

[B_atWork · Accepted solution]

How did I miss that? Yep, that works. Thank you for pointing that out.