Forum Discussion
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! :smileyhappy:
- 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 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 ;)
5 Replies
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 ;)
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. :smileyhappy:
Once I do the explicit conversion to True/False, then it comes through to the table fine.
- 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
MFelixMFelix - thanks very much!!! I got my Binary and True/False mixed up! Rookie mistake.... :smileyhappy:
Back to school for me....