Custom Column for Multiple IF & And Statements

[aliciab425]

The below formula is cauclated in Excel. I would like to see if this would be easily calculated in Power BI, however, I currently have AST1, AST2, and AST3, I need to add AST4, AST5, and AST6. Is this something easily formulated in Power BI?

Here is the current Excel formula with illustrationfor AST1, AST2, and AST3. I do not have an illustration with the additional AST4, AST5, and AST6 because the formula becomes more complex. 

IF(AND(A2="BLANK1",B2="BLANK2",C2="BLANK3"),"Investigate",IF(AND(A2=B2,A2=C2),A2,IF(AND(A2<>"BLANK1",B2="BLANK2",C2="BLANK3"),A2,IF(AND(A2=C2,B2="BLANK2"),A2,IF(AND(A2<>C2,A2<>"BLANK1",C2<>"BLANK3",B2="BLANK2"),"House",IF(AND(A2<>"BLANK1",B2<>"BLANK2",A2<>B2,C2="BLANK3"),"House",IF(AND(A2="BLANK1",B2<>"BLANK2",C2="BLANK3"),B2,IF(AND(A2="BLANK1",B2<>"BLANK2",B2<>C2),"House",IF(AND(A2="BLANK1",B2="BLANK2",C2<>"BLANK3"),C2,IF(AND(A2<>"BLANK1",B2<>"BLANK2",C2<>"BLANK3",A2<>B2,B2<>C2),"House",IF(AND(A2="BLANK1",B2=C2),B2,"ERROR")))))))))))

AST1	AST2	AST3	Expected Result	Line Item Formula	Comp Territory
BLANK1	BLANK2	BLANK3	Investigate	Investigate	Investigate
T1	T1	T1	T1	T1	T1
T1	BLANK2	BLANK3	T1	T1	T1
T1	BLANK2	T1	T1	T1	T1
T1	BLANK2	T2	House	House	House
T1	T2	BLANK3	House	House	House
BLANK1	T2	BLANK3	T2	T2	T2
BLANK1	T2	T1	House	House	House
BLANK1	BLANK2	T2	T2	T2	T2
T1	T2	T3	House	House	House
BLANK1	T2	T2	T2	T2	T2

Thank you in advance!

[AlexisOlson · Accepted solution]

If I understand your logic correctly, this should work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvJx9PM2VNKBMIxgDGOlWJ1opRCQBIyAC+BUCZfAqjrECMlMNO1wV+CXgJqL6WYMs0OwageqigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AST1 = _t, AST2 = _t, AST3 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        let
            L = List.Distinct(List.Select({[AST1],[AST2],[AST3]}, each not Text.StartsWith(_, "BLANK")))
        in
            if List.Count(L) = 0 then "Investigate"
            else if List.Count(L) > 1 then "House"
            else L{0},
        type text)
in
    #"Added Custom"

This takes the list of values in AST1, AST2, AST3 and removes the items starting with "BLANK". Then it removes any duplicates and returns different values depending on how many items remain.