Excel Help Taking a Sheet with Multiple Breaks and turning it into a flattened version

[Taffalaffa]

Hi.  I have been fighting with this problem for a few months and trying to find a solution, but nothing seems to work quite right.  Every month the accounting team updates their regional spreadsheet (tab one in the attachment).  This sheet is broken out by region.  All the columns are the same per region but there are lots of random spaces between different sections.  I need to make this into a flattened sheet that I can turn into a table for my PowerBI reports (see the desired results on tab 2).  Note: getting accounting to change their methods is not an option. I have tried!  Please help me!

I have linked a small version sample for your review & comment.

https://www.dropbox.com/s/msd7y7ac2xago23/Test%20Sheet.xlsx?dl=0

[#2 · Accepted solution]

@Taffalaffa Since you intend to work in PowerBI, use its Power Query (PQ) function to transform the data for you.

Connect to the REGIONAL file on your system and perform a few simple steps t create the table you need. The attached file is based on your Test Sheet. All you need to do is point the Source step to the correct location and file.

The table in the Query tab is the end result.

[#3]

@Riny_van_Eekelen This is fantastic!  Exactly what I needed.  Thank you so much for the help!