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




View solution in original post

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




View solution in original post

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!




View solution in original post

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




View solution in original post

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 360 members 4,001 guests
Please welcome our newest community members: