Change Column type based on cell content

[Mic1979]

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 tried this code:

#"Changed Type3" = Table.TransformColumnTypes(
#"Reordered Columns",
{{
each if [#"Labour Time (MATURE)"] = "TBD"
then type text
else type number
}}),

but I got an error.

Could you please support?

Thanks in advance for your help.

[Jai-Rathinavel · Accepted solution]

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

[v-pgoloju]

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

[rohit1991 · Accepted solution]

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.

[Jai-Rathinavel · Accepted solution]

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

[ZhangKun]

You can remove the conversion step of column X (the column you expect) or replace the type with type any.

Note that if you expect the results of column X to be exported to the model, this is invalid because all values ​​of a single column in the model (or Power Pivot) will be converted to the same type. You can only output different types of values ​​in the same column when using Power Query in Excel and exporting directly to a worksheet.

[SundarRaj]

Hi @Mic1979 , instead of using the if condition, you can simply convert the data type of that column to "any" or simply write this in your Table.TransformColumnTypes function rather than using a text or number. Any will accomodate both text and number values accordingly. Thanks
Something like this: