Need help Converting an Excel formula to Power Query that checks for duplicates from a combination of 2 columns one row above and below

Question

I have an excel formula that looks like this =if(OR([@person]&[@month]]=A1&C1,[@person]&[@month]]=A3&C3), "Duplicate",0)

[@person] is in columnA and [@month] is in columnC

How the excel formula works is it will look at the combination of the person and month in the current row and check if the combined values one row above or below is the same. If same, return "Duplicate" if not return 0.

I'm not sure how to approach this task to convert this idea into power query. I dont know how to write a column formula that can simply refer to the person and month formula one row above and below like in excel.

Thanks in advance

I tried applying an index column to help with creating the formula, but Im not really sure how to apply it. Then i tried using chatgpt to create the formula, but it didnt work . Would like to try and learn it the proper way.

Answer 1

I assume your two columns are named Person and Month. Simplest method is,

Add column ... index column

Add column ... custom column with formula

= try if [Person]&[Month] = #"Added Index"{[Index]-1}[Person]&#"Added Index"{[Index]-1}[Month] then "duplicate" else null otherwise null

Add column ... custom column with formula

= try if [Person]&[Month] = #"Added Index"{[Index]+1}[Person]&#"Added Index"{[Index]+1}[Month] then "duplicate" else null otherwise null

click select those two new columns, right click and merge them

this would probably be faster

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "PersonMonth", each [Person]&[Month]),
shiftedList = {null} & List.RemoveLastN(#"Added Custom"[PersonMonth],1),
shiftedList2 = List.RemoveFirstN(#"Added Custom"[PersonMonth],1),
custom1 = Table.ToColumns(Source) & {shiftedList} & {shiftedList2},
custom2 = Table.FromColumns(custom1,Table.ColumnNames( Source) & {"Up","Down"}),
#"Added Custom1" = Table.AddColumn(custom2, "Custom", each if [Person]&[Month] = [Up] or [Person]&[Month] = [Down] then "Duplicate" else null)
in #"Added Custom1"

Answer 2

You're doing right adding Index Column. Assuming your two columns are named person and month.

Step 1: Add an Index column.

Step 2: Add a Custom column and input this custom formulas (Replace "Table1" with your table name)

let
    currentIndex = [Index],
    currentCombination = [person] & [month],
    prevCombination = if currentIndex > 0 then Table1{currentIndex - 1}[person] & Table1{currentIndex - 1}[month] else null,
    nextCombination = if currentIndex < Table.RowCount(Table1) - 1 then Table1{currentIndex + 1}[person] & Table1{currentIndex + 1}[month] else null
in
    if currentCombination = prevCombination or currentCombination = nextCombination then "Duplicate" else 0