Results 1 to 5 of 5

Thread: Add calculated field based on alternating row values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add calculated field based on alternating row values

    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.
    Direction Email
    Outbound adam.zapple@xyz.com
    Inbound pepper.mintz@abcd.com
    Outbound ella.vator@lmnop.com
    Inbound gerry.actrick@abcd.com
    Outbound adam.zapple@12345.com
    Inbound mel.tingpoint@abcd.com

    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)
    Direction Email FROM TO
    Outbound adam.zapple@xyz.com adam.zapple@xyz.com pepper.mintz@abcd.com
    Inbound pepper.mintz@abcd.com ignore ignore
    Outbound ella.vator@lmnop.com ella.vator@lmnop.com gerry.actrick@abcd.com
    Inbound gerry.actrick@abcd.com ignore ignore
    Outbound adam.zapple@12345.com adam.zapple@12345.com mel.tingpoint@abcd.com
    Inbound mel.tingpoint@abcd.com ignore ignore

    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 by JugglerJAF; Jul 3rd, 2018 at 08:03 AM. Reason: (edited Excel version number)
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Add calculated field based on alternating row values

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add calculated field based on alternating row values

    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")


    Direction Email FROM TO
    Outbound adam.zapple@xyz.com adam.zapple@xyz.com pepper.mintz@abcd.com
    Inbound pepper.mintz@abcd.com ignore ignore
    Inbound pepper.mintz@abcd.com ignore ignore
    Outbound ella.vator@lmnop.com ella.vator@lmnop.com gerry.actrick@abcd.com
    Inbound gerry.actrick@abcd.com ignore ignore
    Outbound adam.zapple@xyz.com ignore ignore
    Outbound adam.zapple@12345.com adam.zapple@12345.com mel.tingpoint@abcd.com
    Inbound mel.tingpoint@abcd.com ignore ignore

    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.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Add calculated field based on alternating row values

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add calculated field based on alternating row values

    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.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •