cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 126 members 1,760 guests
Please welcome our newest community members: