Forum Discussion
Remove Duplicate data and keep the rows as per latest revision
Hi ROBINMECH ,
let
Source = #table(
{
"LINE_ID",
"REV_NO",
"ALL DATA.PROJECT",
"ALL DATA.DRAWING_ID",
"ALL DATA.SPOOL_NO",
"ALL DATA.EPIC_SPOOL_NO"
},
{
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP06", "01414-42-007533"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-ER", "01414-42-001172"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP01", "01414-42-001173"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP02", "01414-42-001173"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP03", "01414-42-001174"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP08", "01414-42-007535"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP07", "01414-42-007534"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP04", "01414-42-001175"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP05", "01414-42-001176"},
{"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP03", "01414-42-001174"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP04", "01414-42-001175"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP05", "01414-42-001176"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP06", "01414-42-007533"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP07", "01414-42-007534"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP08", "01414-42-007535"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-ER", "01414-42-001172"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP02", "01414-42-001173"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP08", "01414-42-007535"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP07", "01414-42-007534"},
{"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP06", "01414-42-007533"},
{"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP05", "01414-42-001176"},
{"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP04", "01414-42-001175"},
{"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP02", "01414-42-001173"},
{"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP01", "01414-42-001172"},
{"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-ER", "01414-42-001172"},
{"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP03", "01414-42-001174"}
}
),
#"Grouped Rows" = Table.Group(Source, {"ALL DATA.EPIC_SPOOL_NO"}, {{"All", each _, type table [LINE_ID=text, REV_NO=text, ALL DATA.PROJECT=text, ALL DATA.DRAWING_ID=text, ALL DATA.SPOOL_NO=text, ALL DATA.EPIC_SPOOL_NO=text]}}),
Tables = Table.TransformColumns ( #"Grouped Rows" , { "All" , each Table.FirstN ( _ , 1 ) } )[[All]],
Table = Table.ExpandTableColumn(Tables, "All", {"LINE_ID", "REV_NO", "ALL DATA.PROJECT", "ALL DATA.DRAWING_ID", "ALL DATA.SPOOL_NO", "ALL DATA.EPIC_SPOOL_NO"}, {"LINE_ID", "REV_NO", "ALL DATA.PROJECT", "ALL DATA.DRAWING_ID", "ALL DATA.SPOOL_NO", "ALL DATA.EPIC_SPOOL_NO"})
in
Table
