Forum Discussion
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 together where the column types could be set while expanding them. How would I go about this?
--CODE--
#"Expanded Sheets" = Table.ExpandTableColumn(#"Previous Step", "Sheets", {"Timestamp", "Reason", "Value", "UOM", "Date", "Time"}, {"Timestamp", "Reason", "Value", "UOM", "Date", "Time"}),
// Additional step to change data types:
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Sheets", {
{"Timestamp", type datetime},
{"Reason", type text},
{"Value", type number},
{"UOM", type text},
{"Date", type date},
{"Time", type time}
})
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.
1 Reply
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.