Question

You import a supplier price list from an Excel file into Power Query. The data has 500 valid product rows, but the source appends 3 summary footer rows at the bottom (containing totals and a 'Report generated' note) that vary in count each month and break your numeric type conversions. You need a repeatable transformation that loads only the genuine product rows regardless of exact footer count. Which approach best achieves this?

Accepted Answer

Filter the rows by keeping only those where the product ID column is not null, so summary/footer rows without a product ID are excluded.

Answer

Use 'Remove Bottom Rows' and hard-code the number 3 to delete the footer rows each refresh.

Answer

Apply 'Keep Top Rows' set to 500 so only the product rows remain after refresh.

Answer

Change the numeric column type to 'Text' so the footer rows no longer cause conversion errors.