I have close to 400 excel files that contain client pricing information. The piece of work is to transform these into multiple database tables to load into Azure SQL Database. I'm tasked with the transformation.

The existing data follows a level of logic. 1 workbook per client. A single client can have multiple ICP's (shown in the example). What I'm looking for is some assistance to use Power Query to transform the data from the source to target formats. Once in Target, it'll be loaded in bulk to SQL server to perform additional transformation into required SQL tables.

Rows that need to be selected

• Customer Name
• Supply Address
• ICP
• Any subsequent row within the selected range (between ICPs) that contain data within the "Old Rates" or "New Rates" columns. Even if a single value is stored in one or the either, both columns for that row require extraction.

Example of source and target