Power BI -- return data based on previous column value in query editor

Question

I have encountered this issue

1. If the value in the second column is "Agree" or "Strongly Agree", the corresponding cell in the third column should be empty (null).

2. If the value in the second column is "Disagree" or "Strongly Disagree", the corresponding cell in the third column should display the value from the next row of the second column.

Can someone kindly take a look and help me solve this? Thanks!!

I have tried using two index columns to merge (the first index column starts from 0, the second index column starts from 1), but this doesn't work

---

The new issue arises when I added the "Add Shifted Answer" step

Previous step works fine..

Answer 1

You did not specify what you wanted returned in the third column if the second column contained neither Agree nor Disagree. I returned a null, but you can easily change that.

The algorithm consistis of adding a "shifted" column so that we are using a replacement on the same row, instead of using an Index column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Question Category", type text}, {"Answer Category", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Answer Category", Text.Trim, type text}}),
    #"Add Shifted Answer" = Table.FromColumns(
        Table.ToColumns(#"Trimmed Text") &
         {List.RemoveFirstN(#"Trimmed Text"[Answer Category],1) & {null}},
         type table[Question Category=text, Answer Category=text, Shifted Answer=text]),

//Note use of Text.Lower as M-Code is case sensitive
    #"Add Result" = Table.AddColumn(#"Add Shifted Answer", "Result", each if Text.Lower([Answer Category])="agree" or Text.Lower([Answer Category])="strongly agree"
            then null 
            else if Text.Lower([Answer Category])="disagree" or Text.Lower([Answer Category])="strongly disagree"
            then [Shifted Answer] else null, type nullable text),
    #"Removed Columns" = Table.RemoveColumns(#"Add Result",{"Shifted Answer"})
in
    #"Removed Columns"

Original Data

Results