Boolean puzzle in Power Query

[Anonymous]

I can't believe this is not working for me.....

I am creating a new column in Power Query as follows:

= Table.AddColumn(#"Removed Columns", "StpsInclude", each if [OrganisationID] = 29 then false else true)

That creates a column consisting of true and false values - in italics, capitalised and right justified - which seems to indicate they are boolean values. However, the column type is still set to "ABC123" i.e. the General type.

When I try to convert the column to Boolean, as below, I get an error - "DataFormat.Error: We couldn't convert to Binary. Details: TRUE"

= Table.TransformColumnTypes(#"Added Custom",{{"StpsInclude", type binary}})

So I ignored that and went straight to the designer and load the column into a table. When I highlight the column in the FIELDS list, it tells me that the column is Text! And it shows the values as 0 or 1, left justified!

Can anyone tell me what's going on? 

I could of course use values other than true and false, but now I'm just annoyed by the whole thing! 

[MFelix · Accepted solution]

Hi @Anonymous

A true or false column is a bolean value as you say so on query editor you need change your true pr false by 1 or 0 then convert it to true or false should work.

Regards
MFelix

[ImkeF · Accepted solution]

Or you can convert it to type logical (instead of type binary).

BTW: You can omit the condition in your statement. Just write 

Table.AddColumn(#"Removed Columns", "StpsInclude", each  [OrganisationID] = 29)

and true and false will be created automagically 😉

[Anonymous]

@ImkeF - indeed it does! Neat trick.

Sadly, when you bring the column through onto a table visual, it treats it as Text data type. In Power Query it is shown as an Any data type. So, it converts the data type automagicallywrongly. 

Once I do the explicit conversion to True/False, then it comes through to the table fine.

[MFelix]

@ImkeF,

Always learning M language from your posts 🙂 🙂 🙂

Regards,
MFelix

[MFelix · Accepted solution]

Hi @Anonymous

A true or false column is a bolean value as you say so on query editor you need change your true pr false by 1 or 0 then convert it to true or false should work.

Regards
MFelix

[Anonymous]

@MFelix - thanks very much!!! I got my Binary and True/False mixed up! Rookie mistake.... 

Back to school for me....