cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
belvoir Member
Member

Boolean puzzle in Power Query

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! Smiley Happy

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Boolean puzzle in Power Query

Hi @belvoir

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Boolean puzzle in Power Query

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 Smiley Wink

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
Highlighted
Super User
Super User

Re: Boolean puzzle in Power Query

Hi @belvoir

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Boolean puzzle in Power Query

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 Smiley Wink

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Boolean puzzle in Power Query

@ImkeF,

Always learning M language from your posts :-) :-) :-)

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




belvoir Member
Member

Re: Boolean puzzle in Power Query

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

 

Back to school for me....

belvoir Member
Member

Re: Boolean puzzle in Power Query

@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. Smiley Happy

 

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