Add calculated field based on alternating row values

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
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.
DirectionEmail
Outboundadam.zapple@xyz.com
Inboundpepper.mintz@abcd.com
Outboundella.vator@lmnop.com
Inboundgerry.actrick@abcd.com
Outboundadam.zapple@12345.com
Inboundmel.tingpoint@abcd.com

<tbody>
</tbody>

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)
DirectionEmailFROMTO
Outboundadam.zapple@xyz.comadam.zapple@xyz.compepper.mintz@abcd.com
Inboundpepper.mintz@abcd.comignoreignore
Outboundella.vator@lmnop.comella.vator@lmnop.comgerry.actrick@abcd.com
Inboundgerry.actrick@abcd.comignoreignore
Outboundadam.zapple@12345.comadam.zapple@12345.commel.tingpoint@abcd.com
Inboundmel.tingpoint@abcd.comignoreignore

<tbody>
</tbody>

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.
 
Last edited:

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
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")


DirectionEmailFROMTO
Outboundadam.zapple@xyz.comadam.zapple@xyz.compepper.mintz@abcd.com
Inboundpepper.mintz@abcd.comignoreignore
Inboundpepper.mintz@abcd.comignoreignore
Outboundella.vator@lmnop.comella.vator@lmnop.comgerry.actrick@abcd.com
Inboundgerry.actrick@abcd.comignoreignore
Outboundadam.zapple@xyz.comignoreignore
Outboundadam.zapple@12345.comadam.zapple@12345.commel.tingpoint@abcd.com
Inboundmel.tingpoint@abcd.comignoreignore

<tbody>
</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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Starting with the below table (in Excel called Emails)



Try the below M:

Code:
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

Active Member
Joined
Feb 17, 2002
Messages
264
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.
 

Forum statistics

Threads
1,077,849
Messages
5,336,736
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top