Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Change Column type based on cell content

Hello,   I would like to change the type of the cells based on their content. In fact I can have the situation where these cells could be a number or a text "TBD" when I don't know the value.   I...
Need Help
  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    1 year ago

    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,

    Jai Rathinavel | LinkedIn

  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Mic1979,

Thank you for reaching out to the Microsoft Fabric Forum Community.
And aslo thanks to rohit1991 , Jai-Rathinavel , ZhangKun and SundarRaj For Prompt and useful response.

 

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

 

Best regards,

Prasanna Kumar