Power Query Function error when joining

Question

I am receiving this error There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression

I created the function below that maps a new column based on the values of 4 input columns. The function works on the table that i applied it to. However when i join the table to another table i receive the above error.

All of the inputs to the function are text columns. if someone has some experience with this it would be a great help.

// FNStage1Transform

let Stage1 = (term,investment_universe,Risk_tolerance,Special) =>

let 
    Stage1Transform =

if List.Contains({"Short","Intermediate"},term) and List.Contains({"Short","Aggregate","Gov/Credit"},investment_universe ) then term
else if term ="Full" then 
 if investment_universe = "Treasury" then term&" "&  investment_universe 
 else if investment_universe ="TIPS" then "Full+Long"&" "&investment_universe 
 else if investment_universe ="Muni" and [Risk Tolerance] = "Conservative" then investment_universe 
 else if List.Contains({"Credit","Corp"},investment_universe) then "CredCorp"
 else investment_universe
else if term  = "Long" and  investment_universe ="TIPS" then "Full+Long"&" "&investment_universe 
else if term  = "Long" and List.Contains({"Credit","Corp"},investment_universe) then term &" "&investment_universe
else if investment_universe  = "Muni" and Risk_tolerance  = "Conservative" then investment_universe 
else if List.Contains({"Credit","Corp"},investment_universe) then term&" CredCorp"
else term &" "&  investment_universe,


Stage2Transform = if List.Contains({"High Beta","Transition"},Risk_tolerance) then Risk_tolerance 
else if Special = "Yes" then Stage1Transform &" "& Risk_tolerance & " Special"

else if Risk_tolerance = "Standard" then Stage1Transform
else Stage1Transform &" "&Risk_tolerance,


Stage3transform = "PS_"& Stage2Transform,



Result = Stage3transform 





in Result

in
    Stage1

Carl Walsh Carl Walsh · Accepted Answer · 2016-05-20T02:44:50

If you search for [ in your code you'll find the error:

else if investment_universe ="Muni" and [Risk Tolerance] = "Conservative" then investment_universe

You want

else if investment_universe ="Muni" and Risk_tolerance = "Conservative" then investment_universe

Power Query Function error when joining

1 Answers