Forum Discussion
Remove Duplicate data and keep the rows as per latest revision
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
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
7 Replies
slorinSuper User
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-aatheequeCommunity Support
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.
- SundarRaj
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.
