Forum Discussion
Missing Columns
Hi there, I have a table of 12 or so columns of numeric and text data, I applied a number of steps and then finally added a column using this m code "RAG = Table.AddColumn(#"Reordered Columns", "Custom", each if [Target] = null or [Result] = null then null
else if [Polarity] = "Higher is better" and [Result] >= [Target] then 1
else if "Higher is better" and [Result] >= ([Target]*0.95) then 0
else if "Higher is better" then -1
else if [Polarity] = "Lower is better" and [Result] <= [Target] then 1
else if "Lower is better" and [Result] <= ([Target]*1.05) then 0
else if "Lower is better" then -1
else null)"
I applied the query and when I looked in Data view the only object I could see was the m code. Has the added column somehow hidded the other columns?
5 Replies
Thanks Nate, Much appreciated.
Mila
Hi Milagros
Did you put the it with the quotes in Advanced Editor? If yes, like Anonymous said, you need to get rid of " ", and you also missed some [Polarity] =
RAG = Table.AddColumn( #"Reordered Columns", "Custom", each if [Target] = null or [Result] = null then null else if [Polarity] = "Higher is better" and [Result] >= [Target] then 1 else if [Polarity] = "Higher is better" and [Result] >= ([Target] * 0.95) then 0 else if [Polarity] ="Higher is better" then - 1 else if [Polarity] = "Lower is better" and [Result] <= [Target] then 1 else if [Polarity] ="Lower is better" and [Result] <= ([Target] * 1.05) then 0 else if [Polarity] ="Lower is better" then - 1 else null )Probably can try it
RAG = Table.AddColumn( #"Reordered Columns", "Custom", each if [Target] = null or [Result] = null then null else if ([Polarity] = "Higher is better" and [Result] >= [Target]) or ([Polarity] = "Lower is better" and [Result] <= [Target]) then 1 else if ([Polarity] = "Higher is better" and [Result] >= [Target] * 0.95) or ([Polarity] = "Lower is better" and [Result] <= [Target] * 1.05) then 0 else if [Polarity] = "Higher is better" or [Polarity] = "Lower is better" then - 1 else null )Hi Vera, Thank you so much, that seems to work perfectly!
Mila
