Duplicating Queries & Source Files
Reuse a query you already built by duplicating it and pointing it to a new source file — then learn why auto-created steps can silently break your data, and how to fix them.
One of Power Query's biggest productivity gains is that you never have to rebuild a transformation from scratch. If you have a second file with a similar structure, you can duplicate an existing query and simply redirect it to the new source — all your steps carry over instantly.
This lab introduces a real-world gotcha: Power Query sometimes auto-creates "Changed Type" steps that hard-code specific column names based on what it sees at import time. If the new file has different columns or date ranges, those steps silently fail or drop data. You'll learn to identify these fragile steps, delete them, disable auto type detection, and apply data types manually — giving you full control over your queries.
| # | Steps | Directions |
|---|---|---|
| 1 | Duplicate the first query. | |
| 2 | Click on the duplicate and modify the source to File 1B. | Use the formula bar to change the referenced source file. |
| 3 | Click on the final step to see what the result looks like. Great — but we don't see the data for 2018 months? | |
| 4 | Change the Source step so that it doesn't specify number of columns. | Delete Columns=19 from the formula bar. |
| 5 | Click on the final query step. The query now returns all the data. | |
| 6 |
But let's consider what would happen if we had fewer columns than we'd started with.
|
|
| 7 | Remove both Changed Type steps that were auto-created. | Delete both Changed Type and Changed Type1 steps. |
| 8 | Change the settings so that auto-steps are not created. | |
| 9 | Change data type of Store and Cat to whole number. | |
| 10 | Rename the query to this slide's title. | |