Replace string and number booleans to native booleans

[Guillaume_]

I have a few datasets where there's a mix of inconsistent boolean, ie Y/N, Yes/No, True/False, 1/0

I would like to convert all these fields to a genuine boolean type TRUE/FALSE

I've tried using Power Query steps but it takes a few steps and have to repeat the same process for every single field.

what would be the best approach to do this? I assume a custom function should be able to handle this easily?

[amitchandak]

@Guillaume_ , You need first make this column as Text . Convert all values True/false or 1/0 and then change data type to boolean

Power Query Replace Value: https://youtu.be/hkZhZbR7Kmk

[Guillaume_]

Thanks the replace value function if fine but replace one value by another so it seems I have to potential run several steps per field to be converted.

Is there a way to encapsulate a set of deterministc steps that would be repeated over several fields across several table? ie a custom function?

[Anonymous · Accepted solution]

Hi @Guillaume_ 

If these values you mentioned in same column, you can directly create a measure with SWITCH() to return the value. For example :

Original data :

Then create a measure like this 

Measure = SWITCH(TRUE(),SELECTEDVALUE('Table'[Y/N])="Y","TRUE",SELECTEDVALUE('Table'[Y/N])="N","FALSE", 
                 SELECTEDVALUE('Table'[Y/N])="Yes","TRUE",SELECTEDVALUE('Table'[Y/N])="No","FALSE",      
                 SELECTEDVALUE('Table'[Y/N])="True","TRUE",SELECTEDVALUE('Table'[Y/N])="False","FALSE",
                     SELECTEDVALUE('Table'[Y/N])="1","TRUE",SELECTEDVALUE('Table'[Y/N])="0","FALSE")

The final result is as shown :

If they are in different columns, then you can create measures based on the columns.

I have attached my pbix file , you can refer to it .

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