if Function in Custom Column returns inconsistent result - what am I doing wrong ?

[Anonymous]

Hello everyone, I'm new to Power BI and having trouble with what should be a fairly simple if statement, so would really appreciate some help.

I need to create 2 new columns Admin and Meetings to split out the General Minutes data based on this criteria:

General, where TaskId is blank and Type = 14.   

Meetings where TaskId is blank and Type is either 1, 2 or 8.

I got this far:

= Table.AddColumn(#"Added Conditional Column2", "General", each if [TaskId] = null and [Type] = 14 then [Minutes] else "")

This doesn't return anything.

= Table.AddColumn(#"Added Custom", "Meetings", each if [TaskId] = null and [Type] = 1 or [Type] = 2 or [Type] = 8 then [Minutes] else "")

This strangely returns [Minutes] for types 2 and 8 but not 1, so I'm confused.

TaskId	Minutes	Type	Task Minutes	General Minutes	Admin	Meetings
234378	30	4	30
234378	24	12	24
252970	6	5	6
252503	6	3	6
234378	180	13	180
234378	30	3	30
NULL	60	14		60
NULL	90	1		90
NULL	60	14		60
NULL	450	8		450		450
NULL	450	8		450		450
NULL	90	2		90		90
NULL	480	8		480		480
NULL	480	8		480		480

Any help would be very welcome, thanks

Andy

[Anonymous · Accepted solution]

Hi @Anonymous 

Based on the sample data you have offered,  you need to make sure that:

The data type of the taskid is whole number,  and the minutes type should also be whole number, it should be like the following piture

Then you can try the following code in custom column.

General:

if [TaskId]=null and [Type] = 14 then [Minutes] else null

Meetings:

if [TaskId] = null and ([Type] = 1 or [Type] = 2 or [Type] =8 ) then [Minutes] else null

Output

And you can refer to the following code in Advanced Editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5REgARCIavsuPZAxVyFuP+11i17DA8eCjl+/+qFANImNhYg64lGsWjUe0kAIEeRjUpAuQkptgi9HfBwWH/xh2P+Z5lhse/PGj0NtyPlKnqHpdFN7OsrKPsbm0UpOXOvu4O6g44LiRefHwD6ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskId = _t, Minutes = _t, Type = _t, #"Task Minutes" = _t, #"General Minutes" = _t, Admin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskId", Int64.Type}, {"Minutes", Int64.Type}, {"Type", Int64.Type}, {"Task Minutes", Int64.Type}, {"General Minutes", Int64.Type}, {"Admin", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "General", each if [TaskId]=null and [Type] = 14 then [Minutes] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Meetings", each if [TaskId] = null and ([Type] = 1 or [Type] = 2 or [Type] =  then [Minutes] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Admin"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous 

Based on the sample data you have offered,  you need to make sure that:

The data type of the taskid is whole number,  and the minutes type should also be whole number, it should be like the following piture

Then you can try the following code in custom column.

General:

if [TaskId]=null and [Type] = 14 then [Minutes] else null

Meetings:

if [TaskId] = null and ([Type] = 1 or [Type] = 2 or [Type] =8 ) then [Minutes] else null

Output

And you can refer to the following code in Advanced Editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5REgARCIavsuPZAxVyFuP+11i17DA8eCjl+/+qFANImNhYg64lGsWjUe0kAIEeRjUpAuQkptgi9HfBwWH/xh2P+Z5lhse/PGj0NtyPlKnqHpdFN7OsrKPsbm0UpOXOvu4O6g44LiRefHwD6ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskId = _t, Minutes = _t, Type = _t, #"Task Minutes" = _t, #"General Minutes" = _t, Admin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskId", Int64.Type}, {"Minutes", Int64.Type}, {"Type", Int64.Type}, {"Task Minutes", Int64.Type}, {"General Minutes", Int64.Type}, {"Admin", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "General", each if [TaskId]=null and [Type] = 14 then [Minutes] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Meetings", each if [TaskId] = null and ([Type] = 1 or [Type] = 2 or [Type] =  then [Minutes] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Admin"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Great, that works fine, thank you for your help Yolo !