Forum Discussion
Change Column type based on cell content
Hi Mic1979 Just replace your "#Changed Type3" with the below expression
#"Changed Type3" = Table.TransformColumnTypes( #"Reordered Columns", {{"Labour Time (MATURE)", type any}} )Hope it helps !
Thanks,
Hi Mic1979 ,
The error you're encountering is due to the way Table.TransformColumnTypes works—it expects a static list of column names and types, not a conditional expression. In Power Query (M), you can't assign different data types to individual cells within a column; types are applied at the column level. If your column contains both numbers and a placeholder like "TBD", the best approach is to cast the entire column as type text, then convert valid numbers back where needed during analysis or use conditional logic to handle "TBD" values. Alternatively, you can create a new column where you try to convert each value to a number and keep it as null or fallback text if it fails, using try ... otherwise. Let me know if you’d like a code example for this workaround.
Hi Mic1979 ,
The error you're encountering is due to the way Table.TransformColumnTypes works—it expects a static list of column names and types, not a conditional expression. In Power Query (M), you can't assign different data types to individual cells within a column; types are applied at the column level. If your column contains both numbers and a placeholder like "TBD", the best approach is to cast the entire column as type text, then convert valid numbers back where needed during analysis or use conditional logic to handle "TBD" values. Alternatively, you can create a new column where you try to convert each value to a number and keep it as null or fallback text if it fails, using try ... otherwise. Let me know if you’d like a code example for this workaround.