When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.

When you bring in structured data the layout lends itself to figuring out the headings and the data types – see image below.

The two steps on the right, highlighted in yellow, have been automatically inserted into the Query. You can delete these steps using the X on the left of each step.

The headers were promoted, which means the names in the first row of the data become the column headers of the data.

The column data types have also been set as Date, Text, Integer and Integer – see icons in the left of the headings.

You may not want this to happen and there is a setting you can change to stop this automatically happening – see Settings section below.

Sometimes you need to manually enter the column headers. This occurs when the first row contains an entry that may change e.g. a date.

The Changed Type step means the data type for each column is estimated based on the contents of that column.

Data Types

When doing more complex work in Power Query the column data types can affect your transformations. For example you need a Date data type on a column you are doing date calculations with.

In some cases it can be better for you to manually set your column data types and not rely on Power Query guessing them correctly.

Settings

You can change a Global setting to stop Power Query creating the two steps highlighted in yellow in the image above.

This will affect all the Power Queries you create after changing this setting.

In the Data ribbon tab click the Get Data drop down and choose Query Options at the bottom.

In the Global Data Load Type Detection section, select the Never detect column … option and click OK.

That’s it.

The steps to Promote Headers and Changed Type will no longer be automatically added.

Also, when you promote the headers yourself using the Use First Row as Headers icon, the Changed Type step won’t be added.