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.
No types on columns
Did you know there is actually a superfast and easy way to do it?
- Click the mouse anywhere in the table
- Press Ctrl + a (check all)
Check the whole table with Ctrl + a
- Go to the Transform-tab ad choose: “Detect Data Type”
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 😉
Thank, Imke! For those in the US, Strg is the Ctrl key. I had to Google it.
Thanks for the heads-up, Steve!
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.
Thanks Mark!
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?
😉
Hi Alex,
there is an option in the Power BI settings that is set on by default. Simply deselect it:
Hi Imke,
it’s so nice of you to answer the comments!
I’d really like to know whether an equal type can be assigned dynamically to all columns (w/o spelling out their names like “Column1”, “Column2″…). (I believe this should be possible via changing the table type.)
I know this is out of scope of this post, but could you give me a hint?
Hi again :),
I found a good solution from the function lib:
= List.Transform(Table.ColumnNames(my_tbl), each {_, Text.Type})
Hi Ben,
if my understanding of your request is correct, I might have covered you here: https://www.thebiccountant.com/2019/12/18/advanced-transformation-multiple-columns-at-once-in-power-bi-and-power-query/
Cheers, Imke
Hi Imke!
My solution above was only the argument to the function
Table.TransformColumnTypes. The call I make looks like this:
Table.TransformColumnTypes(my_tbl, List.Transform(Table.ColumnNames(my_tbl), each {_, Text.Type}))
Thanks for the link, but in my case it’s just a dynamic type conversion. First I thought, this would require to access the table type like I saw here now:
https://blog.crossjoin.co.uk/2018/10/03/function-m-table-type/
Remark:
I think dynamic handling of columns is a bit untypical in query languages, because you know the column names at implementation time already. It can even be risky to accept any name and number of columns like I do above.