Adding a custom column based on multiple other columns using Power Query

Larix

New member
Joined
Apr 28, 2021
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2016
Hi there,
I'm trying to solve a puzzle using power query. I'd like to create a custom column that returns the values "yes" or "no" depending on the values in several other columns. If the Year Since NPF column = "1" and the 1 column = "y" my custom column should return "yes" and so on.

I have tried using the following code"

if [Years since NPF] = 1 or 2 then "yes"
else if [Years since NPF] = 3 and [3] = "y" then "yes"
else if [Years since NPF] = 4 and [4] = "y" then "yes"
else if [Years since NPF] = 5 and [5] = "y" then "yes"
else if [Years since NPF] = 6 and [6] = "y" then "yes"
else if [Years since NPF] = 7 and [7] = "y" then "yes"
else if [Years since NPF] = 8 and [8] = "y" then "yes"
else if [Years since NPF] = 9 and [9] = "y" then "yes"
else if [Years since NPF] = 10 and [10] = "y" then "yes"
else if [Years since NPF] = 11 and [11] = "y" then "yes"
else if [Years since NPF] = 12 and [12] = "y" then "yes"
else if [Years since NPF] = 13 and [13] = "y" then "yes"
else if [Years since NPF] = 14 and [14] = "y" then "yes"
else if [Years since NPF] = 15 and [15] = "y" then "yes"
else if [Years since NPF] = 16 and [16] = "y" then "yes"
else if [Years since NPF] = 17 and [17] = "y" then "yes"
else if [Years since NPF] = 18 and [18] = "y" then "yes"
else if [Years since NPF] = 19 and [19] = "y" then "yes"
else if [Years since NPF] = 20 and [20] = "y" then "yes"


else "no"

but get an error saying "Expression. Error: We cannot convert the value "2" to type Logical.
Details:
Value=2
Type=Type"

Here is an example of my data below:

Years since NPFPlant Species1234567891011121314151617181920
1Norway MapleyyYy
4Norway MapleyyYy
1Norway MapleyyYy
10Norway MapleyyYy
1Norway MapleyyYy
3Norway MapleyyYy
2Norway MapleyyYy
4Pineapple Weedyyyyy yy
7Pineapple Weedyyyyy yy
2Pineapple Weedyyyyy yy
2Pineapple Weedyyyyy yy
5Pineapple Weedyyyyy yy
1Pineapple Weedyyyyy yy
2Pineapple Weedyyyyy yy
2Pineapple Weedyyyyy yy
2Pineapple Weedyyyyy yy
11Yellow hawkweedyyyyyyy
7Yellow hawkweedyyyyyyy
3Yellow hawkweedyyyyyyy
7Yellow hawkweedyyyyyyy


I realize this might be very simple but any help would be SO appreciated! Thank you!
 
The "or" in the beginning is wrong.

if [Years since NPF] = 1 or
[Years since NPF] = 2 then "yes" ...
 
Or without that complexe IF-condition:

1 - Query: Load_Input

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1)
in
#"Added Index"

2. Query: Result

let
Source = Load_Input,
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"5", type text}, {"7", type text}, {"8", type text}, {"10", type text}, {"12", type text}, {"13", type text}, {"16", type text}, {"17", type text}, {"18", type text}, {"19", type text}}, "de-DE"),{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Combined"),
#"Replaced Value" = Table.ReplaceValue(#"Merged Columns","Y","y",Replacer.ReplaceText,{"Combined"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","y;","y",Replacer.ReplaceText,{"Combined"}),

#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Years since NPF] < 3 or Text.Middle([Combined], [Years since NPF]) = "y" then "y" else "-"),

#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Years since NPF", "Plant Species", "Combined"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Index"}, Load_Input, {"Index"}, "Load_Input", JoinKind.LeftOuter),
#"Reordered Columns" = Table.ReorderColumns(#"Merged Queries",{"Index", "Load_Input", "Custom"}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Reordered Columns", "Load_Input", {"Years since NPF", "Plant Species", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"}, {"Years since NPF", "Plant Species", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded {0}",{"Index"})
in
#"Removed Columns1"
 
The correct IF-Condition is:

each if [Years since NPF] < 3 or Text.Middle([Combined], [Years since NPF] - 1, 1) = "y" then "y" else "-")
 
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text}) ),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each if Number.From([Years since NPF]) < 3 then "y" else if Record.Field(_, [Years since NPF]) = "y" then "y" else "-"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Years since NPF", Int64.Type}})
in
    #"Changed Type1"
 
Back
Top