Add calculated field based on alternating row values

[JugglerJAF]

I have a data table that looks something like this (fake data obviously) that's been pulled from a text file and brought into Excel using the Power Query AddIn for Excel 2013.
[TABLE="class: grid, width: 276"]
<tbody>[TR]
[TD]Direction[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD]adam.zapple@xyz.com[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD]pepper.mintz@abcd.com[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD]ella.vator@lmnop.com[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD]gerry.actrick@abcd.com[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD]adam.zapple@12345.com[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD]mel.tingpoint@abcd.com[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is to create a calculated field within the Power Query environment that would look at each row and return the following (logic used to get to this is explained after the table)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 90"]Direction[/TD]
[TD="width: 186"]Email[/TD]
[TD="width: 175"]FROM[/TD]
[TD="width: 169"]TO[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD]adam.zapple@xyz.com[/TD]
[TD]adam.zapple@xyz.com[/TD]
[TD]pepper.mintz@abcd.com[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD]pepper.mintz@abcd.com[/TD]
[TD]ignore[/TD]
[TD]ignore[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD]ella.vator@lmnop.com[/TD]
[TD]ella.vator@lmnop.com[/TD]
[TD]gerry.actrick@abcd.com[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD]gerry.actrick@abcd.com[/TD]
[TD]ignore[/TD]
[TD]ignore[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD]adam.zapple@12345.com[/TD]
[TD]adam.zapple@12345.com[/TD]
[TD]mel.tingpoint@abcd.com[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD]mel.tingpoint@abcd.com[/TD]
[TD]ignore[/TD]
[TD]ignore[/TD]
[/TR]
</tbody>[/TABLE]

So, for each row, if the direction is "Outbound" and the direction on the next row is "Inbound", then populate the "FROM" column with the email address from the row and also populate the "TO" column with the email address from the next row. If both criteria are not met, then populate both columns with "ignore".

It's easy enough to do this with a formula in Excel once the data has been brought through by Power Query, but I'd like to do it within the Power Query environment, so that I can then apply an additional filter to the data within the query to filter out any records in the "FROM" column that are equal to "ignore" which will reduce by half the amount of data that's being populated into Excel.

I've tried Googling for a solution, but everything I find includes the instruction "In the Query Editor ribbon, click Insert Custom Column.", but I don't seem to have that option on my ribbon menu.

 

[VBA Geek]

Can you post an example of when the criteria is not met?

 

[JugglerJAF]

If the direction on the row is "outbound" and the next row's direction is "inbound", then populate FROM with the outbound email from the row and populate TO with the inbound email from the next row. In all other circumstances, populate both fields with "ignore".

If the table below was in Excel...
... the formula in C2 would be: =IF(AND(A2="Outbound",A3="Inbound"),B2,"ignore")
... the formula in D2 would be: =IF(AND(A2="Outbound",A3="Inbound"),B3,"ignore")

<tbody>
[TD="class: xl65"]Direction[/TD]
[TD="class: xl65, width: 167"]Email[/TD]
[TD="class: xl65, width: 167"]FROM[/TD]
[TD="class: xl65, width: 167"]TO[/TD]

[TD="class: xl65"]Outbound[/TD]
[TD="class: xl65"]adam.zapple@xyz.com[/TD]
[TD="class: xl65"]adam.zapple@xyz.com[/TD]
[TD="class: xl65"]pepper.mintz@abcd.com[/TD]

[TD="class: xl65"]Inbound[/TD]
[TD="class: xl65"]pepper.mintz@abcd.com[/TD]
[TD="class: xl65"]ignore[/TD]
[TD="class: xl65"]ignore[/TD]

[TD="class: xl66"]Inbound[/TD]
[TD="class: xl66"]pepper.mintz@abcd.com[/TD]
[TD="class: xl66"]ignore[/TD]
[TD="class: xl66"]ignore[/TD]

[TD="class: xl65"]Outbound[/TD]
[TD="class: xl65"]ella.vator@lmnop.com[/TD]
[TD="class: xl65"]ella.vator@lmnop.com[/TD]
[TD="class: xl65"]gerry.actrick@abcd.com[/TD]

[TD="class: xl65"]Inbound[/TD]
[TD="class: xl65"]gerry.actrick@abcd.com[/TD]
[TD="class: xl65"]ignore[/TD]
[TD="class: xl65"]ignore[/TD]

[TD="class: xl66"]Outbound[/TD]
[TD="class: xl66"]adam.zapple@xyz.com[/TD]
[TD="class: xl66"]ignore[/TD]
[TD="class: xl66"]ignore[/TD]

[TD="class: xl65"]Outbound[/TD]
[TD="class: xl65"]adam.zapple@12345.com[/TD]
[TD="class: xl65"]adam.zapple@12345.com[/TD]
[TD="class: xl65"]mel.tingpoint@abcd.com[/TD]

[TD="class: xl65"]Inbound[/TD]
[TD="class: xl65"]mel.tingpoint@abcd.com[/TD]
[TD="class: xl65"]ignore[/TD]
[TD="class: xl65"]ignore[/TD]

</tbody>

I can do this in Excel (and have done, using a macro to copy the linked data to a new worksheet and then populating the formulas and deleting the unwanted rows), but if I can do it in Power Query, then it means that fewer records will be returned to Excel making the file size smaller and subsequent calculations quicker.

 

[VBA Geek]

Starting with the below table (in Excel called Emails)

Try the below M:

let
    Source = Excel.CurrentWorkbook(){[Name="Emails"]}[Content],
    Indx1 = Table.AddIndexColumn(Source,"Indx1",0,1),
    Indx2 = Table.AddIndexColumn(Indx1,"Indx2",1,1),


    TblJoin = Table.NestedJoin(Indx2,
                               "Indx2",
                                Indx2,
                                "Indx1",
                                "JoinedTbl",
                                JoinKind.LeftOuter),
 


    AddColFROM = Table.AddColumn(TblJoin,
                                 "FROM",
                                  each if 
                                          [Direction] = "Outbound" and Record.Field([JoinedTbl]{0},"Direction") = "Inbound"
                                       then
                                          [Email]
                                       else
                                           "ignore",
                                 type text),


    AddColTO = Table.AddColumn(AddColFROM,
                               "TO",
                               each if 
                                      [Direction] = "Outbound" and Record.Field([JoinedTbl]{0},"Direction") = "Inbound"
                                   then
                                      Record.Field([JoinedTbl]{0},"Email") 
                                   else
                                       "ignore",
                                 type text),




   FinalTable = Table.SelectColumns(AddColTO,{"Direction","Email","FROM","TO"})             
in
    FinalTable

 

[JugglerJAF]

Wow, that's quite a lot to think about. I need to read up a LOT more on this subject.

Thank you so much for your help with this.