Automatically detect and change the types of all columns at once in Power Query

Today I want to share quick tip on how to automatically detect and change all column types at once in Power Query.

Background

Very often, when you expand a column in Power Query that contains a table or some records, the expanded columns will lose their types (like Chris Webb has described here for example). Or you might just have accidently deleted a “Changed Type”-step.

change all column types at once

No types on columns

Did you know there is actually a superfast and easy way to do it?

  1. Click the mouse anywhere in the table
  2. Press Ctrl + a (check all)
change the types of all columns

Check the whole table with Ctrl + a

  1. Go to the Transform-tab ad choose: “Detect Data Type”
change all column types at once

Transform with 1 click

Voila: All your columns should have types on them.

They have been automatically been detected by checking the first 100 rows of your table. So if you know that you’re having columns with inconsistent values in them, make sure to check the automatically assigned values.

Enjoy & stay queryious 😉

Comments (6) Write a comment

  1. Discovered recently (PQ in Excel), select columns you wish to be same type using Ctrl or shift key. Then while holding shift select Abc123 icon on one of the selected columns and get to change type for all selected columns to this type.
    Fully expect it to be there for PQ PBI too.

    Reply

  2. Hi all,

    Thanks for the post and the tricks out there! Those were nice!

    I think what is different in the approaches is the make it possible to get rid of that extra step of changing the type. As when anytime you click the interface button, it generates the extra code.

    And if the code is big and complex and on the big data tables, it can take a while to update it and affects performance.

    What do you think?

    😉

    Reply

    • Hi Alex,
      there is an option in the Power BI settings that is set on by default. Simply deselect it:
      Automatic type detection is set on by default

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz