Logical Data Type Custom Column in Power Query

[Bob_W]

I have created a custom column, which generates a 1 or 0 for each field based on a simple if statement, that I've defined as a Logical Data type.

My issue is that when applied and viewed in Power BI as a Data table the column is entirely empty and generates a data type error. However, if I simply change the data type to "Int64.Type" then it works fine.

Hopefully someone can tell me where this is going wrong please?

#"Mrg: dim_cust_inter_cmpny" = Table.NestedJoin(#"Table1", {"CustName"}, dim_cust_inter_cmpny, {"Customer"}, "dim_cust_inter_cmpny", JoinKind.LeftOuter),
        #"Expd: dim_cust_inter_cmpny" = Table.ExpandTableColumn(#"Mrg: dim_cust_inter_cmpny", "dim_cust_inter_cmpny", {"Inter Company"}, {"dim_cust_inter_cmpny.Inter Company"}),
#"Cus_Col: Inter_Co" = Table.AddColumn(#"Expd: dim_cust_inter_cmpny", "Inter_Co",
        each if [dim_cust_inter_cmpny.Inter Company] = "Y" then 1
        else 0, Logical.Type)

 

[JGordon11]

Logical type is for true or false, excel will interpret 1 and 0 as true or false and vice versa, but in PQ it looks like you need to use true and false rather than 1 and 0 for logical type. Also syntax I think should be type logical not Logical.Type

#"Cus_Col: Inter_Co" = Table.AddColumn(#"Expd: dim_cust_inter_cmpny", "Inter_Co",
each if [dim_cust_inter_cmpny.Inter Company] = "Y" then true
else false, type logical)

 

[Bob_W]

#"Cus_Col: Inter_Co" = Table.AddColumn(#"Expd: dim_cust_inter_cmpny", "Inter_Co",
each if [dim_cust_inter_cmpny.Inter Company] = "Y" then true
else false, type logical)

Thanks for the reply.

You are right that I need to specify the true/false rather than using 1/0 (thought I had read that was ok in PQ, but apparently not! )

The data type though is "Logical.Type" in the 2020 version of PBI I've got. I was picking it up from the Intelli-sense prompts. Not used earlier versions so maybe that syntax has been changed?

 

[JGordon11]

Thanks for the feedback. I agree, it seems to be a version thing. In my version (PQ in Excel 2019) type logical works and Logical.Type returns an error.