Multiple Condition Nested IF Statement

[msquared99]

In Power Query I have two columns that I need to cleanup some data. In column 1 I have a company ID# and in column 3 I have a service. What I am trying to do is filter out what I do not need.

Example:
Column 1 Column 3
F1972 Farming
F1972 Tractor
C1002 Farming
C1002 Tractor
H1624 Farming
H1624 Tractor
12456 Farming
13595 Farming

What I want to do is say IF column 1 begins with "F" or "C" or "H" and contains "Farming" True else False. This way in the new custom column I can filter out all the True and end up with the below output:

Column 1 Column 3
F1972 Tractor
C1002 Tractor
H1624 Tractor
12456 Farming
13595 Farming

Thanks for anyone's help.

 

[Bob Phillips]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Service", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if ((Text.Start([Id],1)="C" or Text.Start([Id],1)="F" or Text.Start([Id],1)="H") and [Service]<>"Farming") or (Text.Start([Id],1)<>"C" and Text.Start([Id],1)<>"F" and Text.Start([Id],1)<>"H" and [Service]="Farming") then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

 

[msquared99]

Thanks Bob.

I'm having some issues. While it works for some of the criteria it does not for others. I'll post some examples later.

Your code above was very helpful and thank you.

 

[Rudi]

My approach is similar to Bob's, but my conditional statement is slightly different.

Maybe try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Filter", each try if [Service] = "Farming" and (Text.Start([ID],1)="F" or Text.Start([ID],1)="C" or Text.Start([ID],1)="H") then "True" else "False" otherwise "False"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = "False"))
in
    #"Filtered Rows"