Required Comment in New Column based on criteria of other columns in power query

This is a table where I need to find out which invoice material batches were used sequentially or not sequentially.

I need result to be

Inv No Date Inv Count Product Code Batch Batch Serial Required New Column with Comments 9573334429 31/07/2023 1 57663 TAP3F20026 4 Not Traced 9573334430 31/07/2023 1 68691 UIWZ306057 1 Not Traced 9573334431 31/07/2023 0 57822 MKL3D20031 1 Not Traced 9573334431 31/07/2023 0 69422 MKL3D20028 1 Serial 9573334431 31/07/2023 1 69422 MKL3D20032 2 Serial 9573334431 31/07/2023 0 69469 MKL3F20039 1 Not Traced 9573334432 31/07/2023 1 67671 SPR3B20026 5 Not Traced 9573334433 31/07/2023 1 59175 SPR3B20064 2 Not Traced 9573334434 31/07/2023 1 70963 MKL3B23039 3 Not Traced 9573334435 31/07/2023 1 70963 MKL3B23038 2 Not Traced 9573334436 31/07/2023 1 68691 UIWZ306057 1 Not Traced 9573334437 31/07/2023 1 57663 TAP3F20026 4 Not Traced 9573334437 31/07/2023 0 69012 TAP3F20025 4 Not Traced 9573334438 31/07/2023 1 55710 TAP2F20043 3 Not Traced 9573334438 31/07/2023 0 55714 TAP3A20011 3 Not Traced 9573334439 31/07/2023 1 66966 HAP2F20114 2 Serial 9573334439 31/07/2023 0 66966 HAP2F20115 3 Serial

Solution

In the future, you need to provide data and explanation more clearly.

However, this does what you want in PowerQuery: Group by Inv No and Product Code. If there is one row, "Not Traced". If there are multiple rows where Batch Serials is consecutive, "Serial", else "Non Serial"

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Inv No", "Product Code"}, {{"data", each
      let count = Table.RowCount(_),
      difference=List.Difference({List.Min(_[Batch Serial])..List.Max(_[Batch Serial])},_[Batch Serial]),
      process=Table.AddColumn(_, "check", each try if count=1 then "Not Traced" else if List.Count(difference)=0 then "Serial" else "Non Serial" otherwise "Non Serial")
in process, type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Inv Count", "Batch", "Batch Serial", "Required New Column with Comments", "check"}, {"Date", "Inv Count", "Batch", "Batch Serial",    "Required New Column with Comments", "check"})
in  #"Expanded data"