locked
Can you create a custom column in power query that will run a formula that makes a comparative cell reference within a column? RRS feed

  • Question

  • Hello and thank you in advance for any advice,

    I am new to Power Query and trying hard to learn the ropes.  I have a formula that I used to run in excel, but I am now using Power Query for the data set.  I have sampling events with an ID (in one column) that have an effort (in a separate column), and sometimes they are duplicated, which means there is a group ID (in another column).  I would like to create a new effort column for the sampling event in Power Query, but I am not sure if I can do it.  After the appropriate sorting of the data, the formula used to be able to look at different cells within the same column and, if they were equal, return blank or a cell value if they weren't equal.  Like: =IF(AQ5=AQ4,"",AJ5).  Does anyone know if this is possible within Power Query create a custom column?  From what I have found, or the lack thereof, it is not, so I was wondering what other people might think, or if they would know of a work around.

    Thank you so much for reading and helping if you can,

    MFBirdy

    Thursday, January 24, 2019 5:34 PM

Answers

  • Hi

    Like: =IF(AQ5=AQ4,"",AJ5)

    So referencing the previous row within the same column - quite common question

    Suggest to read thread Previous Month Multiple Colums (there are others if you search) where several options are discussed. Depending upon the size of your dataset some options are better than others...

    Imke's function offers - in addition to perf. - the possibility to not only ref. row +/-1 but more if you need
    As you read and test please Upvote the appropriate options. Same here + Mark as Answer if no more help required with this

    Thursday, January 24, 2019 6:02 PM

All replies

  • Hi

    Like: =IF(AQ5=AQ4,"",AJ5)

    So referencing the previous row within the same column - quite common question

    Suggest to read thread Previous Month Multiple Colums (there are others if you search) where several options are discussed. Depending upon the size of your dataset some options are better than others...

    Imke's function offers - in addition to perf. - the possibility to not only ref. row +/-1 but more if you need
    As you read and test please Upvote the appropriate options. Same here + Mark as Answer if no more help required with this

    Thursday, January 24, 2019 6:02 PM
  • Hello and thank you for your help,

    Your link did provide me with ideas, but I ultimately found a work around in that I created a new column so that if there was a group ID it would populate with that and if Group ID was blank it would populate using the sampling event ID, and then through some group commands, I was able to summarize the data as I needed.  Power Query is great, but it is important to open up to the new ways of handling data rather than trying to use the older general excel ways.

    I appreciate your time,

    MFBirdy

    Tuesday, January 29, 2019 7:28 PM
  • Hi

    Glad you found your own way to address your issue + agree re. Excel way of doing things
    Thanks for posting back

    Wednesday, January 30, 2019 10:27 AM