Missing Columns

[Milagros]

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?    

[BA_Pete · Accepted solution]

Hi @Milagros ,

Very difficult to diagnose based on the info provided.

Could you share a couple of screenshots of what your PQ step list looks like, and what it looks like in the Data View please?

Pete

[Anonymous]

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
  )

[Milagros]

Hi Vera, Thank you so much, that seems to work perfectly!

Mila

[Anonymous · Accepted solution]

Just get rid of the first and last quotes (")

--Nate

[Milagros]

Thanks Nate, Much appreciated.

Mila

[BA_Pete · Accepted solution]

Hi @Milagros ,

Very difficult to diagnose based on the info provided.

Could you share a couple of screenshots of what your PQ step list looks like, and what it looks like in the Data View please?

Pete