Remove Duplicate data and keep the rows as per latest revision

[ROBINMECH]

Dear Members,

I have a power query table on which data values are duplicate on each row except the revision. I want a solution only to keep the rows as per latest revision of the line number. See below sample table .

1.Current Data

2. New Data Required

This is my current step

[SundarRaj · Accepted solution]

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

[v-aatheeque]

Hi @ROBINMECH ,

Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @ROBINMECH ,

If the information addressed by @SundarRaj  @slorin  is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[slorin]

When you group you can choose: sum, min, max... but not "first"
So you choose Sum but then you change the code

= Table.Group(Your_Source, {a list of columns}, {{"New column", each List.SumFirst([YourColumn]), type text}})

 Stéphane

[v-aatheeque]

Hi @ROBINMECH ,

We noticed that a solution provided by @SundarRaj to your query regarding retaining only the latest revision per line number in your Power Query table. If the solution helped resolve your issue, Marking it as Accepted Answer so others with similar challenges can benefit from it.

If you’re still facing issues or need further clarification, please feel free to drop a comment under the thread we’ll be happy to assist further.

Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[slorin]

Hi @ROBINMECH 

Group, choose Sum then replace List.Sum by List.First

Stéphane

[ROBINMECH]

@slorin Thanks for the quick response.

I'm not that expert in the power query script. Could you please guide me on which step I should choose "Sum"