Forum Discussion

Rowboat99's avatar
Rowboat99
New Member
2 years ago
Solved

Can I set column types while using Table.ExpandTableColumn?

--QUESTION-- The below code shows two steps used. The first expands columns from table 'Sheets' and the second step sets the data types for the columns. I wish to be able to join these steps togeth...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Rowboat99 ,

    Unfortunately, in Power Query, the actions of expanding a table column and transforming column types are inherently two separate steps. The Table.ExpandTableColumn function is used to normalize nested tables by expanding them into multiple rows and columns, while the Table.TransformColumnTypes function is specifically designed to change the data types of columns. You cannot use one of these functions nested within another.
    Also, in my tests, if your data columns are formatted more clearly (e.g., date only, date + time, or time only), Power Query automatically recognizes their data types based on your data format and maintains your original data column types after ExpandTableColumn, so you don't need to manually modify all the columns in general.

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Rowboat99 ,

Unfortunately, in Power Query, the actions of expanding a table column and transforming column types are inherently two separate steps. The Table.ExpandTableColumn function is used to normalize nested tables by expanding them into multiple rows and columns, while the Table.TransformColumnTypes function is specifically designed to change the data types of columns. You cannot use one of these functions nested within another.
Also, in my tests, if your data columns are formatted more clearly (e.g., date only, date + time, or time only), Power Query automatically recognizes their data types based on your data format and maintains your original data column types after ExpandTableColumn, so you don't need to manually modify all the columns in general.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.