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

Question

This is a table where I need to find out which invoice material batches were used sequentially or not sequentially, in simple words to find out which invoices were billed in multiple batches in a single invoice Whether or not the FEFO method was used for single material.

I need result to be

For upload to Power Query 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 9573334440 31/07/2023 0 40362 TAP2J20881 1 Not Traced 9573334440 31/07/2023 1 40363 TAP3B20091 2 Not Traced 9573334441 31/07/2023 1 22256 MKL2H20282 1 Not Traced 9573334443 31/07/2023 1 55717 TAP2K20065 5 Not Traced 9573334444 31/07/2023 1 55714 TAP3A20011 3 Not Traced 9573334444 31/07/2023 0 55717 TAP2K20065 5 Not Traced 9573334445 31/07/2023 0 22228 MKL2C20090 2 Not Traced 9573334445 31/07/2023 0 22232 MKL3D20035 1 Not Traced 9573334445 31/07/2023 0 22247 MKL2G20201 2 Not Traced 9573334445 31/07/2023 0 57663 TAP3E20017 9 Not Traced 9573334445 31/07/2023 0 57811 MKL3C20006 3 Not Traced 9573334445 31/07/2023 1 57822 MKL3D20021 3 Not Traced 9573334445 31/07/2023 0 63934 TAP2L20003 2 Not Traced 9573334445 31/07/2023 0 65902 TAP2K20060 4 Not Traced 9573334445 31/07/2023 0 73194 GM23F1701 2 Not Traced 9573334445 31/07/2023 0 73286 TAP3E20015 3 Not Traced 9573334445 31/07/2023 0 75784 TAP3C26056 2 Not Traced 9573334446 31/07/2023 0 22228 MKL2C20088 1 Not Traced 9573334446 31/07/2023 0 22247 MKL2G20200 1 Not Traced 9573334446 31/07/2023 1 57822 MKL3D20021 3 Not Traced 9573334446 31/07/2023 0 63925 TAP2E20068 3 Not Traced 9573334446 31/07/2023 0 63934 TAP2L20003 2 Not Traced 9573334446 31/07/2023 0 63935 TAP2L20008 1 Not Traced 9573334446 31/07/2023 0 66231 TAP2F20019 6 Not Traced 9573334446 31/07/2023 0 69288 MKL2D20014 2 Not Traced 9573334446 31/07/2023 0 73166 MKL2E20032 2 Not Traced 9573334446 31/07/2023 0 73286 TAP3E20015 3 Not Traced 9573334447 31/07/2023 1 75254 MKL3G20028 5 Not Traced 9573334449 31/07/2023 1 70963 MKL3B23039 3 Not Traced 9573334450 31/07/2023 1 70963 MKL3B23039 3 Not Traced 9573334451 31/07/2023 1 65768 MKL2C20012 1 Not Traced 9573334451 31/07/2023 0 65777 MKL2B20004 1 Not Traced 9573334451 31/07/2023 0 65778 MKL2B20001 1 Not Traced 9573334452 31/07/2023 0 65777 MKL2B20004 1 Not Traced 9573334452 31/07/2023 1 65778 MKL2B20001 1 Not Traced 9573334453 31/07/2023 1 56297 ABS2C2J097 3 Not Traced 9573334454 31/07/2023 1 70968 MKL3B23024 14 Not Traced 9573334455 31/07/2023 0 54144 MKL3F23817 14 Not Traced 9573334455 31/07/2023 1 54147 MKL3F23821 3 Not Traced 9573334456 31/07/2023 0 63934 TAP2L20003 2 Not Traced 9573334456 31/07/2023 1 63935 TAP2L20008 1 Not Traced 9573334457 31/07/2023 1 36682 ABS3D2J151 1 Not Traced 9573334457 31/07/2023 0 36685 ABS3E2J208 2 Not Traced 9573334457 31/07/2023 0 40362 TAP3B20091 1 Not Traced 9573334457 31/07/2023 0 40363 TAP3B20091 3 Not Traced 9573334457 31/07/2023 0 75254 MKL3G20027 4 Not Traced 9573334458 31/07/2023 1 68388 MKL3D20007 1 Not Traced 9573334462 31/07/2023 1 74612 MKL2G20064 5 Not Traced 9573334463 31/07/2023 1 75735 AUN23VC081 12 Not Traced 9573334464 31/07/2023 1 69147 MKL3B20021 2 Not Traced 9573334464 31/07/2023 0 69148 MKL3B20011 1 Not Traced 9573334465 31/07/2023 1 36686 ABS3E2J199 1 Serial 9573334465 31/07/2023 0 36686 ABS3E2J202 2 Serial 9573334466 31/07/2023 0 74612 MKL3D20019 8 Non Serial 9573334466 31/07/2023 0 74612 MKL3G20057 12 Non Serial 9573334466 31/07/2023 0 74612 MKL3G20057 12 Non Serial 9573334466 31/07/2023 1 74612 MKL3G20058 13 Non Serial 9573334467 31/07/2023 0 55963 MKL3B23039 1 Not Traced 9573334467 31/07/2023 1 57822 MKL3D20031 3 Not Traced 9573334467 31/07/2023 0 74731 MKL3B23042 2 Serial 9573334467 31/07/2023 0 74731 MKL3C23065 3 Serial 9573334468 31/07/2023 1 63925 TAP2E20068 4 Not Traced 9573334469 31/07/2023 0 69422 MKL3D20025 26 Not Traced 9573334469 31/07/2023 0 69469 MKL3G20045 6 Not Traced 9573334469 31/07/2023 0 74731 MKL3C23067 5 Not Traced 9573334469 31/07/2023 1 74732 MKL3C23062 2 Not Traced 9573334470 31/07/2023 0 55961 MKL3B23035 2 Not Traced 9573334470 31/07/2023 0 57822 MKL3G20051 5 Not Traced 9573334470 31/07/2023 1 69422 MKL3D20025 26 Not Traced 9573334470 31/07/2023 0 69469 MKL3G20045 6 Not Traced 9573334470 31/07/2023 0 74612 MKL2G20060 1 Non Serial 9573334470 31/07/2023 0 74612 MKL2G20064 5 Non Serial 9573334470 31/07/2023 0 74731 MKL3C23066 4 Serial 9573334470 31/07/2023 0 74731 MKL3C23067 5 Serial 9573334470 31/07/2023 0 74732 MKL3C23062 2 Not Traced 9573334471 31/07/2023 1 74732 MKL3C23062 2 Not Traced

horseyride · Accepted Answer · 2023-08-01T18:22:43.297

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"

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

1 Answers1